Bob here.  We do a lot of work with SQL.  In fact that’s ourprimary role with several of our customers. Maintaining, troubleshooting, all that good stuff.  And one thing is absolutely clear:

A lot of organizations don’t know how tomeasure/maintain/troubleshoot SQL server performance.  And it’s understandable.  It can be a complicated configuration, onewith many pieces and parts.  And if anyone of them isn’t optimally configured…stuff slows down to a crawl.

And the first thought is “let’s add hardware!”  And although that usually will improveperformance, it often masks what the real problem is.  Poorly formed queries, outdated statistics,server level configurations are just some of the examples.We’ve seen many examples where tweaking just one aspect of adatabase results in tenfold performance improvement.

But you need to have a solid understanding of what’s goingon under the hood.  You have to have a baseline measurement.  And here are sometools that are built in that can help.

If you’re not seeing the performance from your SQL environment, contact us here at Simplex-IT.  We can help.

This article excerpt, by Dan Popescu, originally appeared here:
Using the SQL Profiler trace log – this powerful utility is one of the best to use for troubleshooting MS SQL Server’s performance. SQL Profiler is used to monitor all activity running on the Server. With this tool you can analyze data before bottlenecks are made, making it easier to determine the root cause. It is also useful for troubleshooting slow queries and responses and to determine bad application configurations. If your server handles many calculations, it is suggested that the Index Tuning Wizard tool be used. The Index Tuning tools optimizes SQL queries, determines if indexes are used properly, and can also be used to increase the overall performance of the SQL server.
Blocker script output – a mechanism that once configured properly can be used to easily determine system blockings or troubleshoot performance problems. Data gathered from blocker scripts can be incorporated into monitoring software for better analysis and troubleshooting. We won’t touch on how to implement blocker scripts because this can be quite complicated and could require an entirely separate article.

SQL Server Performance Monitor log – the performance tool available with MS SQL.
Using it you can monitor performance counters that are responsible for bottlenecks like memory and CPU usage, system blocking, etc. For troubleshooting the server’s performance we have to enable the performance counters. Gathering data can be done on the local SQL Server or it can be sent to the monitoring tool on a remote machine. Make sure that the following data is included in the analysis (information taken from Microsoft’s website): Paging file, Process, Processor, All SQL Server counters, Memory, Threads, Logical disk, Physical disk and System.