During your career as a DBA, you’ll run across articles by SQL experts or other DBAs that just give you an idea. A few years ago I found an article, or a poster to be more precise, that explains SQL Server performance counters. It was written by Kevin Kline, Brent Ozar, Christian Bolton, Bob Ward, Rod Colledge, and Raoul Illyaos. http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf I was thinking this would be a nice poster for any DBA to hang up in their cubical. But as I read down near the bottom, I saw they also mentioned getting performance counters from within SQL Server via sys.dm_os_performance_counters. ...
Continue reading...Performance
SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations
Have you seen this type of an informational message in your SQL errorlog before? If so, then I hope the timestamp associated with it was not during the middle of your peak processing time. This is a good indication that someone has been tinkering with some of the SQL configuration settings. SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service. The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell...
Continue reading...Get Performance Counters from within SQL Server
I think most DBAs know that you can use the Windows Performance Monitor to capture performance metrics for your SQL Server. But did you know that you also have access to all the SQL Server performance counters from within SQL Server? By using using the DMV sys.dm_os_performance_counters, you can view that performance data. As with all DMVs, there are a few drawbacks. The data collected is only valid since the last time SQL Server was started, and this DMV does not allow access to any of the Window OS performance counters. For example, we can query the DMV to find...
Continue reading...Capture Index Usage Statistics for Better Analysis
Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views to help DBAs see what’s going inside of SQL Server. One of my favorite is the sys.dm_db_index_usage_stats. It collects statistics for different types of index operations and the timestamp at which it was last performed. All the DMVs are great tools; however, they all have the same drawback. They are memory resident, which means the statistics are reset any time the SQL Server service is restarted, the database is attached/detached, or it’s restored. The best way to avoid losing this valuable data is to store it in a persistent table. ...
Continue reading...Which SQL Instance is Killing Your CPU?
You have 5 instances running. How do you know which one is killing your CPU? As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU. To figure this out, you’ll need to know the PID (Process ID) number. To view the PID number in Windows Task Manager, from the menu select View, then Select Columns, and then check the box for PID. For our example, it’s Process ID 1212 that’s using nearly 50% of our CPU. So how do you correlate that PID...
Continue reading...
