MSSQL has many excellent tools which can be used for discovering performance bottlenecks.
Sp_monitor and dbcc perfmon
To get a snapshot of what the SQL Server is doing then these commands are very usefull.
Run sp_monitor twice with a gap of 30 seconds in between to get a high level overview of how busy the sql server is.
dbcc perfmon will give you much more detailed information on the state of the sql server but the command has been supervised by various SQL Server counters which can be used in the System monitor.
SSMS Reports
Sql Server has a set of excellent reports which are generated from the Dynamic Management Views, these are both at the server and database level. If you right click on the server or an individual database in the SQL Server Management Studio then you can select which report to run. You can even export the report output to a pdf. This will highlight any problems which you can then focus on.
System monitor
To drill down further the first port of call should be system monitor or perfmon, which is started by typing perfmon into the run box.
One excellent feature of this is that you can export to a Counter Log at the same time as running a SQL Profiler session, (discussed later), you can then merge them which will let you see how bad queries affect the CPU, memory etc or vice versa.
The objects to normally check for in system monitor are:
Memory, Network Interface, Physical Disk, Processor, SQLServer:Buffermanager, SQLServer:Databases, SQLServer:Exec Statistics, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks, SQLServer:Memory Manager, SQLServer:PlanCache, SQLServer:SQL Statistics and System.
SQL Profiler
The system monitor might highlight the bad performance but you will typically need to drill down a bit further to find the bad queries which are causing the problems. To do this you use SQL Profiler.
First step is to establish a performance baseline as follows;
– Start Profiler, Select file, New trace and connect to the relevant instance.
– Specify a trace name, template(standard) and a location to save the trace file too. Set the maximum fiel size to 50MB and enable file rollover.
– In the events selection tab remove Audit Login, Audit Logout, ExistingConnection and SQL:BatchStarting radio boxes.
– Click on show all columns and select the TextData, NTUserName, LoginName, CPU, Reads, Writes, Duration, SPID, StartTime, EndTime, BinaryData, DatabaseName, ServerName and ObjectName coulumns.
– Click on Column Filters and select the Database name to be the database you are interested in.
– Click Run to start the trace and simultaneously start the System Monitor Counter Log.
Correlating the counter Log with Profiler Trace
– Start Profiler, Select File, Open and select the trace file you created from the Performance baseline previously.
– Next go to File, select Import Performance Data and select the Counter Log file you created simultaneously with the performance baseline trace.
– In the performance Counters Limit Dialog window select Network Interface: Output Queue Length, Processor:% processor Time, System:Processor Queue Length and SQLServer:Buffer Manager:Page life expectancy. Click OK.
– You can then scroll through the trace and find what was happening at peak activity on the Windows machine and also what effect certain commands had on the overall server performance.