Monday, May 4, 2009 at 12:02AM This query uses the dynamic management queries in SQL Server 2005. It analyzes and calculates a “measured_improvement” factor that is generally the best bang-for-buck when deciding which index recommendation to implement. So I would normally do a sort like this:
Sunday, April 19, 2009 at 3:19PM When I need to defrag large databases or tables with high rates of fragmenting without affecting performance on the server for too long I use this proc to incrementally defrag tables, getting the worst fragmented tables done first.
Sunday, April 19, 2009 at 2:44AM Re-indexing tasks take a heavy toll on performance. Here we can see that, even on a new cluster with ample power, SQL Server is still slowed to a crawl with latch wait times > 4sec and high disk queues even on a new dedicated SAN. Maintenance windows are almost mandatory in most production systems as this type of performance drain is rarely acceptable during regular business hours. In the case of “never-slow” requirements the re-indexing can be managed in a more granular approach as to limit SQL Server from an all out re-indexing of a database. There will be a follow up to this post called “Omnipotent Re-indexing Visualized – Is there a better way?” showing one method of granular re-indexing control.
Sunday, April 12, 2009 at 1:18AM Log Shipping DBCC commands issues during log restores here is showing to be flushing the cache every 15 minutes. This can sometimes cause intermittent performance issues that are hard to identify without correlating the cache hit levels to the performance lag.
Saturday, March 28, 2009 at 6:18PM Using the SQL Server Performance Dashboard reports we identify here a statement that only takes several ms to run, but is run 120,000 times a day, is improved on immensely by adding a clustered index to a table that only had 1000 records. Usually not significant for small tables, the fact that the statement was called so often created a 80% cup utilization, and then down to 40% after the small clustered index was applied.
Tuesday, March 17, 2009 at 12:58AM High checkpoint pages/sec can hurt performance regarding logs and i/o wait times. Several factors can contribute to checkpoint pages being written to disk at high rates. The correlations are easy to identify with other activity at the same time.
--by Alek Kirstein – SupportSQL.com
Sunday, March 1, 2009 at 3:17PM Issue: Drastic performance drain reported by users staying late and through high timeouts alerting around 7pm.
Graph explanation: Each bar chart shows the wait stats for resources for each day of the week for weeks included. Here one week is included and the time frame that identifies the performance drain is at 7pm starting the week a new job was implemented. Traffic patterns tend to normalize across the time of day, and can be analyzed for trend abnormalities pointing to underlying and maybe as yet unknown problems.
Possible issues that can be found are: dense job firings, business related spikes, new application effects on the trend baseline, finding open windows for job/maintenance scheduling, etc.
Analysis: In this case we can clearly identify the new job is creating high cxpacket waits (due to linked server as found in the execution plan) and generating what seems excessive update locks (due to recursive update queries and heavy indexing). Locking is a garden of optimization opportunity and due to the various data providers implemented in SQL Server and their various settings, locking techniques can usually be improved with significant immediate benefit.
Notes: Using a Unified Scale for all charts in this multi-chart layout helps identify contrasts to the other charts. This lessens the visual representation for charts that are comparatively small to the chart with the highest values, which is not favorable for trend analysis, but great for representing peaks proportionately.
What is missing from this visual is the multi-week version showing the trend contrast for 19:00. Also missing are the changes to the queries and app code that contributed to the decrease in resource/cpu waits.
--by Alek Kirstein – SupportSQL.com
Monday, February 23, 2009 at 12:16AM Unified scale on these multiple charts are used here to visually see the contrasts between the wait type levels for different hours of the day. This can be use for a specific day for issue analysis, or for several days to visualize average/trend representations.
--by Alek Kirstein – SupportSQL.com
Sunday, February 15, 2009 at 1:14AM Blocking is resolved here by tracing the blocking/blocked spids with activity monitor (206 & 67 below) to the locked object with sp_lock (677577452) then to the object (Revision table) to determine possible causes, here we find that large indexes and multiple indexes are causing long latencies, blocking and timeouts during table updates. An index analysis will show which indexes are not being used and can therefore be eliminated, reducing update times and lowering blocking issues and lock wait timeouts. Low use indexes can also be exchanged for statistics which do not update at the time of data updates, therein lowering SQL Server internal update processing.
—by Alek Kirstein – SupportSQL.com
Sunday, February 8, 2009 at 7:10PM PageSplits are correlated here to the DiskQueue peaks (light-blue) and high wait timeouts (red). Page splits can happen for various reasons, here being that index pages did not have sufficient padding as well as the network i/o packet size was too small for several LOB data type traffic peaks. Increasing the packet size and the index padding to resolved this.
--by Alek Kirstein – SupportSQL.com
Friday, January 30, 2009 at 9:07PM The trend for signal waits as a cpu bottleneck is identified here as a growing trend of CXPACKET waits on synchronizing parallelism streams. A newly deployed application uses many complex views/functions against highly indexed and normalized tables with joins to linked servers. The clincher is that this is an OLTP implementation. The machine is a cluster but an older one and its the cpu that tends to “hit mud” the quickest for this hardware. This trend correlates to the application’s traffic pattern. This gives me a tangible forecast for operations capacity planning and influential reasoning with development engineers to prioritize modifications.
--by Alek Kirstein – SupportSQL.com
Saturday, January 17, 2009 at 1:08AM This is an article in a multi-part series regarding SQL Server performance. The general concept or focus is on analyzing waits in SQL Server to determine bottlenecks and troubleshoot performance problems. This methodology provides quick answers to immediate problems and will serve to forecast capacity planning, traffic ceilings, odd trends and provide a reliability mechanism for database centric applications and the quality control over user experience.
The series comprises these discussion:
Sunday, November 2, 2008 at 5:04PM 
The above are just some of the examples of charts based on the Wait Stats data
(click the thumbnails to see the full size versions on flickr.com)
Description:
SQL Server Dynamic Management Queries provide in-depth details about the waits and wait types that are occurring for internally running sql processes at any given time, as well as over a past period of time since the statistics were cleared or the server restarted. In many administrative functions for SQL Server the identification and resolution of bottle-necks or other performance impeding issues threatening a stable running system are high priority functions and are the center point of effectiveness for the DBA.
Wait statistics in SQL Server is a great way
This work is licensed under a
Creative Commons License.