1. Can we hot add CPU to sql server?
2. How can we check whether the port number is connecting or not on a Server?
TELNET <HOSTNAME> PORTNUMBER
TELNET ADMIN 1433
TELNET ADMIN 1434
Common Ports:
MSSQL Server: 1433
HTTP TCP 80
HTTPS TCP 443
3. What is the port numbers used for SQL Server services?
4. Can we uninstall/rollback a service packs from SQL Server 2005?
No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it.
From SQL Server 2008 you can uninstall a SP from Add/Remove programs.
Some people are saying that we can do it by backup and replace the resource db. But I am not sure about it.
5. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
6. SQL Server is not responding. What is action plan?
Connect using DAC via CMD or SSMS
Connect via CMD
SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
Once you connect to the master database run the diagnostic quires to find the problem
Correct the issue and restart the server
Find the errors from sql log using
SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”
7. What are the Hotfixes and Patches?
Hotfixs are software patches that were applied to live i.e. still running systems. A hotfixis a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug).
In a Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.
Ex: If a select query returning duplicate rows with aggregations the result may be wrong….
8. Why Shrink file/ Shrink DB/ Auto Shrink is really bad?
In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.
The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;
The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
Recommendations:
9. Do you know about Resource Database?
All sys objects are physically stored in resource database and logically available on every database.
Resource database can faster the service packs or upgrades.
10. Really does resource faster the upgrades? Can you justify?
Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file.
We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.
11. I have my PROD sql server all system db’s are located on E drive and I need my resource db on H drive how can you move it?
No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.
12. Can we take the backup for Resource DB?
No way. The only way if you want to get a backup is use windows backup for option resource mdf and ldf files.
13. Any idea what is the Resource db mdf and ldf file names?
mssqlsystemresource.mdf and mssqlsystemresource.ldf
14. How to install a SQL Server using configuration file?
From Command prompt locate the setup.exe file location and can install using config file.
Setup.exe /ConfigurationFile=MyConfigurationFile.INI
Instead of specifying passwords inside the config file specify them explicitly as below.
Setup.exe /SQLSVCPASSWORD=”************” /AGTSVCPASSWORD=”************” /ASSVCPASSWORD=”************” /ISSVCPASSWORD=”************” /RSSVCPASSWORD=”************” /ConfigurationFile=MyConfigurationFile.INI
15. What is the option ”Lock Pages in Memory”?
Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tells Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.
Following issues may rise when “Lock Pages in Memory” is not turned on:
- Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning.
- Starting with SQL Server 2008, SQL Server supports hot add CPU.
- Requires hardware that supports hot add CPU.
- Requires the 64-bit edition of Windows Server 2008 Data-center or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
- Requires SQL Server Enterprise.
- SQL Server cannot be configured to use soft NUMA
- Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.
2. How can we check whether the port number is connecting or not on a Server?
TELNET <HOSTNAME> PORTNUMBER
TELNET ADMIN 1433
TELNET ADMIN 1434
Common Ports:
MSSQL Server: 1433
HTTP TCP 80
HTTPS TCP 443
3. What is the port numbers used for SQL Server services?
- The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.
- The browser service runs on port UDP 1434.
- Reporting services is a web service – so it’s port 80, or 443 if it’s SSL enabled.
- Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.
4. Can we uninstall/rollback a service packs from SQL Server 2005?
No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it.
From SQL Server 2008 you can uninstall a SP from Add/Remove programs.
Some people are saying that we can do it by backup and replace the resource db. But I am not sure about it.
5. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
6. SQL Server is not responding. What is action plan?
Connect using DAC via CMD or SSMS
Connect via CMD
SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
Once you connect to the master database run the diagnostic quires to find the problem
Correct the issue and restart the server
Find the errors from sql log using
SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”
7. What are the Hotfixes and Patches?
Hotfixs are software patches that were applied to live i.e. still running systems. A hotfixis a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug).
In a Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.
Ex: If a select query returning duplicate rows with aggregations the result may be wrong….
8. Why Shrink file/ Shrink DB/ Auto Shrink is really bad?
In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.
The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;
The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
Recommendations:
- Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation.
- Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased
9. Do you know about Resource Database?
All sys objects are physically stored in resource database and logically available on every database.
Resource database can faster the service packs or upgrades.
10. Really does resource faster the upgrades? Can you justify?
Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file.
We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.
11. I have my PROD sql server all system db’s are located on E drive and I need my resource db on H drive how can you move it?
No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.
12. Can we take the backup for Resource DB?
No way. The only way if you want to get a backup is use windows backup for option resource mdf and ldf files.
13. Any idea what is the Resource db mdf and ldf file names?
mssqlsystemresource.mdf and mssqlsystemresource.ldf
14. How to install a SQL Server using configuration file?
From Command prompt locate the setup.exe file location and can install using config file.
Setup.exe /ConfigurationFile=MyConfigurationFile.INI
Instead of specifying passwords inside the config file specify them explicitly as below.
Setup.exe /SQLSVCPASSWORD=”************” /AGTSVCPASSWORD=”************” /ASSVCPASSWORD=”************” /ISSVCPASSWORD=”************” /RSSVCPASSWORD=”************” /ConfigurationFile=MyConfigurationFile.INI
15. What is the option ”Lock Pages in Memory”?
Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tells Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.
Following issues may rise when “Lock Pages in Memory” is not turned on:
- SQL Server performance suddenly decreases.
- Application that connects to SQL Server may encounter timeouts.
- The hardware running SQL Server may not respond for a short time periods.
