The SQL Server Administrator's Console

Monitoring and Administrative Articles and Scripts for the Data Center running with Microsoft SQL Server 2008    

November 2010 - all editorial content is now being posted to the blog at http://bwunder.wordpress.com. Scripts mentioned in the web log will continue to be posted to this page.

Articles

Building a Data Bridge Catalog - protect your data from the bad guys  (any data center, any database server)

Database Monitoring Strategy - project oriented business strategy aligned database monitoring. (SQL Server 2000/2005/2008)

Encrypted Storage of Sensitive DBA Values - store sensitive data like  passwords for encryption hierarchy keys, key backups and database backups in an encrypted column in a highly secure database.  Associated Scripts below. (SQL Server 2008)

Monitoring SQL Server Ring Buffers - system diagnostic information found in SQL Server along with guidelines and examples of XML shredding techniques to get maximum benefit from the information provided in SQL Server ring buffers and DMVs. Associated Scripts below. (SQL Server 2005/2008)

SQL Injection - links and references to help in identifying SQL Injection vulnerabilities and to help DBAs convince IT managers and developers that the application layer is where most SQL Injection vulnerabilities are created. (SQL Server 2000/2005/2008)

Transparent Data Encryption - the easiest way yet to help protect SQL Server data from malicious or unintended uses. Script of working example and discussion about the limitations of TDE. (SQL Server 2008)

What Changed?  - troubleshooting and resolving performance and stability problems. (SQL Server 2000/2005/2008) 

Share Database Knowledge with Office and SQL Server - Combining the automatic versioning of Microsoft Office 'Track Changes' with SQL Server Full Text Search to share knowledge and information. I wrote this article published at SQLServerCentral.com. Here is a copy of the document in Word 2007 (docx) format with track changes enabled for users working through the SQLClue Data Center Runbook tutorial . (SQL Server 2005/2008)   

Scripts

Enable SQLAudit auditing to the Application Event Log

SQLAudit_Application_Event_Log.sql - In most scenarios, there is not a good reason to run a live system without this minimum level of change tracking. (SQL Server 2008)

 

Monitoring SQL Server Ring Buffers 

GetRingBufferDetails.sql - summarizes sys.dm_os_ring_buffers content by type then runs XQueries to shred details of some of the more common ring buffer types into some easy to read and usually informative rowsets. (SQL Server 2005/2008)

 GetXESystemHealthSummary.sql - the extend event "system health" session is a little more expensive to shred. Recall from the article that the nodes are all in one XML document unlike the sys.dm_os_ring_buffers where each row in the DMV contains one event and the XML for only that event. In the interest of saving time the "system heath" summary is provided as a separate query. That way if there is nothing new to look over the painful shredding of a pile of deadlocks that have already been reviewed or a few hundred invalid connection exceptions from an already known source can be foregone. If the summar showns nothing interesting or new, no need to g=fetch the details. (SQL Server 2008) 

 

GetXESystemHealthRecentDetails.sql - If there is something in the sys.dm_xe_session_events XML document that is worth a closer look, it is necessary to partially shred everything just to get the interesting stuff. The details query can take a few minutes to run. The first thing it does is make a working copy of the in-memory XML to assure that there are no contention related side effects. (SQL Server 2008) 

Stored procedure to list the highest resource consuming queries or stored procedures. The Result listings allow a click through to the actual graphical ShowPlan for the currently cached plan used by the query or procedure (when using Results to Grid only). There are getting to be a number of queries for these DMVs around the Internet. What is different and interesting about the queries below is the problem identification feature. For example GetMemory rates memory pressure in a gradient form high to low, the GetWaits ranks waits by type and the GetCPU qualifies CPU pressure from high to low.  

server-wide query performance characteristics 

GetMemory.sql - memory pressure events, buffer pool allocation, object cache allocations and reuse, query plan use, queries with high physical IO, queries with high call rates and queries with lots of reads, all to help understand memory bottlenecks in the context if a selected database query load. (SQL Server 2008)

 

GetWaits.sql - wait type distribution, signal waits -vs- resource waits at the CPU and the longest running queries in a selected database. Provides a view into the systemic resource bottlenecks. (SQL Server 2008)

 

GetCPU.sql - CPU pressure indicators, the biggest CPU using queries server wide and the biggest CPU users in a selected database to evaluate the ability of the configured CPU to support the query load. (SQL Server 2008)

 

GetIO.sql - File use, virtual file stats, queries with most physical reads, most logical reads, most writes and highest call rates to help understand the IO subsystem load profile of a database. (SQL Server 2008)   

• query performance characteristics of individual database objects. 

GetTopRankingObjectStats.sql - the highest resource using stored procedures or triggers in a database by selected performance characteristics with ranking to better understand the load distribution between procedures and triggers in applications with significant trigger usage. (SQL Server 2008)

 

RSGetTopRankingObjectStats.sql - above query modified to better supply data to the Reporting Services report that follows. (SQL Server 2008)

TopRankingObjects.rdl - A Reporting Services report to provide a visual to help identify the performance outliers among the stored procedures and triggers in a database. This report gets its data from the GetTopRankingObjects.sql stored procedure. (SQL Server 2008 R2 November CTP - To use with a pre R2 SQL Server 2008  remove the tag at line 6335 

            <rd:SecurityType>Integrated</rd:SecurityType> 

to get it to load in SQLServer 2008 SP1 BIDS. Then reconfigure DataSource1 to point to a valid SQL Server 2008 SP1 database engine.)

GetObjectStats.sql - see how a selected stored procedure or trigger stacks up to all others by using the great new sys.dm_exec_procedure_stats to sys.dm_exec_trigger_stats DMVs in SQL Server 2008. Includes a drill-through link to the graphical ShowPlan when executed in SQL Server Management Studio. (SQL Server 2008)

ObjectStats.rdl - The charts and tablix of the TopRankingObjects.rdl report above provides drill through to this visual performance report fed by the above query (SQL Server 2008 R2 November CTP - To use with a pre-R2 release of SQL Server 2008 remove the tag at line 4171 

            <rd:SecurityType>Integrated</rd:SecurityType> 

to get it to load in SQL Server 2008 SP1 BIDS. Then remove the dynamic datasource string that works fine in the R2 CTP of BIDS and reconfigure DataSource1 to point to a valid SQL Server 2008 SP1 database engine.)

 

These supporting queries are needed to populate the Schema drop down and the Object dropdown in the parameters of the report. Build these stored procedures in the DBA resource data store on the SQL Server so there is no need to make a connection to any database other than the one dedicated for DBA tools. RSGetShemaList.sql gets a list of schemas with objects in the statistics DMV and RSGetStatsObjectList.sql gets a list of the objects.

GetTableIndexUsage.sql - the actual usage (seeks, scans and changes) of each index on a selected table. (SQL Server 2005/2008)

• monitoring for unused indexes, mis-used indexes, missing indexes, and the heap tables.

GetIndexProblems.sql - the results are interleaved by table to provide a very helpful view of what is and isn't indexed well and what needs to be indexed more effectively. 

• implement columnar encryption in SQL Server as  as described as in the Encrypted Storage of Sensitive DBA Values article.

DBAValues.sql - Script to create an encrypted storage container for sensitive values used by the DBA with Symmetric Key Encryption and TDE

This page was last modified on Friday, November 05, 2010 10:53:03 AM