Skip to content

Monitor Queries against a Table

Posted by on 11 September 2011

I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was occurring, and it would also be nice to be able to associate it to the executing query.

The following code uses DMVs in order to trap a sample of the queries running against it. Please note that this will not trap all queries, however it will work in getting a good number of samples. This script will run in an endless loop so be sure to hit cancel at some point. Also, for some reason the SQL Statement will not always be trapped. It was not that important for me, as I mostly needed the locks, however if someone figures it out, please post.


-- Capture query activity against a table using DMVs
DECLARE @TableName varchar(255);

-- Specify the table you want to monitor
SET @TableName = 'Sales.SalesOrderDetail';

DECLARE @ObjectID int;
SET @ObjectID = (SELECT OBJECT_ID(@TableName));

IF OBJECT_ID('tempdb..##Activity') IS NOT NULL
BEGIN
    DROP TABLE ##Activity;
END;

-- Create table
SELECT TOP 0 *
INTO ##Activity
FROM sys.dm_tran_locks WITH (NOLOCK);

-- Add additional columns
ALTER TABLE ##Activity
ADD SQLStatement VARCHAR(MAX),
SQLText VARCHAR(MAX),
LoginName VARCHAR(200),
HostName VARCHAR(50),
Transaction_Isolation VARCHAR(100),
DateTimeAdded DATETIME;

DECLARE @Rowcount INT = 0;

WHILE 1 = 1
BEGIN

	INSERT INTO ##Activity
	SELECT dtl.*
			,SQLStatement       =
				SUBSTRING
				(
					qt.text,
					er.statement_start_offset/2,
					(CASE WHEN er.statement_end_offset = -1
						THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
						ELSE er.statement_end_offset
						END - er.statement_start_offset)/2
				)
			,qt.text
			,ses.login_name
			,ses.host_name
			,ses.transaction_isolation_level
			,DateTimeAdded = GETDATE()
	FROM sys.dm_tran_locks dtl WITH (NOLOCK)
	LEFT JOIN sys.dm_exec_sessions ses
		ON ses.session_id = dtl.request_session_id
	LEFT JOIN sys.dm_exec_requests er WITH (NOLOCK)
		ON er.session_id = dtl.request_session_id
	OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
	WHERE dtl.resource_associated_entity_id = @ObjectID;

	SET @Rowcount = (SELECT @@ROWCOUNT)
	IF @Rowcount > 100
	BEGIN
		BREAK;
	END;
	-- Wait 50 milliseconds
	WAITFOR DELAY '00:00:00.50';

END

SELECT *
FROM ##Activity
Filed under DBA, DMV Queries, Troubleshooting.
Comment

Execution Plans

Posted by on 11 September 2011

The execution plans SQL Server creates and uses is a huge topic with many intricacies. Now I have never spoken to anyone on the SQL Server Development team, however I would imagine that there are some extremely sharp people in the query optimization team. In a nutshell, this is where the magic happens in SQL Server.

Overview
An execution plan is a behind-the-scenes look at the road a query takes in order to deliver it’s final result. They are generated from the underlying data statistics combined with what the query and it’s parameters are trying to accomplish. When the initial query is read, the execution plan generation engine or “Query Optimizer” searches for the best way to deliver the results of the query in the quickest way possible. To do this, it needs to know what the underlying data looks like. This is facilitated by the statistics that are stored for each table, column, and index. With these statistics in hand, the goal is to eliminate the largest number of records as quickly as possible, and iterate through this process until the final result is delivered. That said, it is not an easy job. There are many variables that come into play when determining a query’s path. A few of these include the selection of indexes, join algorithms, join order, parallelism.

Displaying the Plan
As SQL developers, it is our job to understand how to read execution plans so we can see how the query is being executed. To view an execution plan for a query, select query -> Include Actual Execution Plan. (Or just hold ctrl + M)

Now, the execution plan will be displayed for all queries within this session, until you turn it off. Execute a query and you will see a new tab next to your results that says “Execution Plan”.

Reading
Read the execution plan from right to left. The first paths the query takes are on the right hand side and extend all the way to the left until it gets to the final node. A cost percentage is assigned to each operation.

This gives you a relative understanding of how expensive each operation is for that query. Sometimes this will lead you to find a missing index, modify an existing index, or even break the query up into multiple parts. The cost can sometimes be misleading though. It is not always that the most expensive operation is the slowest or most intensive.

Hovering your mouse over each operation will display additional details about each operation.

These drill downs include valuable information including the index or object being referenced, the columns that are output by the operation, the predicates (or filters), estimated and actual statistics.

Missing Index Hints
Many times, the execution plan will give you hints about what index could be added to speed up the query. You’ll see this at the top of the execution plan in green text.

In order to view the details of the missing index, right click on the green text and choose “missing index details”. This will open a new window with the create statement for the index.

Conclusion
Sometimes, the optimizer will not choose the best execution plan it could for a query. This can be due to a number of reasons, but personally, I try to take those reasons away from the optimizer by breaking my complicated queries up into multiple queries and using temp tables. This helps relieve the optimizer from making bad decisions. With a good amount of practice, you can begin to correlate how the query is written with the way the execution plan is laid out. This does take a lot of trial and error, so don’t be scared to change things like join orders, break things out to temp tables, use exists instead of joins.. This is the best way you will learn what is most efficient.

Filed under SQL Optimization, Troubleshooting.
Comment

Understanding Batch Requests / sec

Posted by on 9 September 2011

SQL Server’s Batch Requests represents the number of SQL Statements that are being executed per second. In my opinion this is the one metric that gives the best baseline on how your server is performing. It cannot be looked at by itself however. It is necessary to correlate with other metrics (especially CPU usage) in order to get an overall understanding of how your server is performing. The overall goal of any SQL Server is to be able to achieve the most batch requests/sec while utilizing the least amount of resources.

On most busy machines I’ve worked with, this counter averaged around 180 – 400 batch requests/sec during peak time. This peak throughput is heavily dependent on the architectural design of the system, including procedures, tables, and index design. One notable example of this was a busy system I worked with whose procedures were written using loops (ugh).. The average batch requests we could ever achieve was around 200. I was very surprised to initially see this, however digging deeper into the code I became less and less shocked. Ultimately I found one piece of code that affected the throughput of the entire system. It was a scalar UDF defined as a computed column in the busiest table on the system (don’t get me started). Anyway, after rewriting that one process, the system then found batch request peaks that went over 3500! The fact that the system could achieve that number now was a big achievement. It meant the overall throughput of the system was dramatically increased 10 fold and not bottle-necked in one spot.

So pay attention to this counter and realize the goal is to achieve the greatest number of batch requests while keeping the resources low (CPU, Disk, Memory).

To add this counter, open perfmon (performance monitor), click This counter can be found in performance monitor under SQLServer:SQL Statistics: Batch Requests/sec.

Batch Requests/sec Counter

Filed under DBA, SQL Optimization.
2 Comments

Understanding SQL Server Deadlocks

Posted by on 30 August 2011

In order to fix deadlocks, it’s critical to understand why they occur. I’ve gotten some push back sometimes from readers when I write articles that do not give cookie cutter solutions, but the “teach a man to fish” adage couldn’t be more true for deadlocks.

All of it revolves around locking, which is a central piece to SQL Server’s default transaction isolation level of read-committed. Now what is read committed? Some people are critical of this isolation level because with a different isolation level like Snapshot, it is much less likely that deadlocks occur because each transaction utilizes it’s own version of what a table looks like at the very time the transaction begins. There are trade-offs with shapshot however. Snapshot isolation is much more disk intensive because these “snapshots” need to be persisted in a temporary data store. So if you are ever considering enabling snapshot isolation as your default isolation level for SQL Server you will want to make sure you have a beefy RAID 10 tempdb storage.

So with read-committed we have less tempdb data swapping, but more locking. There are many different kinds of locks and many different objects that can get locked. Each of these types of locks may or may not be compatible with other types of locks. Locks are acquired so that users have a consistent view of what the data looks like at the time they are querying it. Imagine user 1 begins updating a table to inactivate all the records while user 2 queries this table in the middle of the operation. Without locks, user 2 may see half of the records inactivated, while the other half is activated. With locks, User 2 would have to wait in line until the update is completed. Once completed user 2 will only ever see the result of a completed update rather than a half completed update.

A deadlock happens when two transactions each grab one resource, then the second resource each transaction needs, is the first resource that each of them grabbed. To understand this you can think of the game twister. Each person represents a transaction and each transaction needs a certain color circle to finish their goal. But if the only color each of them can reach is a spot that each of them already occupy, then they are deadlocked.

Troubleshooting
Four things come to mind when deadlocks occur.

  1. Footprint
  2. Coincidence
  3. Transaction Order
  4. Transaction Isolation

In that order. By far the biggest offender I’ve seen on most systems is the footprint issue. By footprint I mean the amount of locks and duration those locks are held by a query. Some queries can acquire a lot of locks yet be done in a short period of time. Most slow queries have a large footprint by means of both locks and duration.

Coincidence is the frequency in which the colliding queries are run. This is also crucial. You can have colliding queries that have a relatively low footprint, however because they run so frequently, the chances of them colliding are much greater. In these cases your best bet is to try and decrease the footprint. If you can’t do that then you may need to look at a design change. Maybe the the procedure can run as a scheduled job rather than being user-invoked.

Transaction order applies if you utilize explicit transactions (BEGIN TRAN.. END TRAN). I try to avoid explicit transactions as much as possible for this reason. Let’s say you have two separate transactions that both update and select records from the same table. If transaction 1 updates before it selects, and transaction 2 selects before it updates, then the chances of these two transactions colliding is greatly increased. You always want to perform the same operations against a table in the same order.

Transaction Isolation is important because it is set at a connection level. It is possible to have rogue application connections come into the database and request a very restrictive isolation level (say serializable). Once an isolation level like this is set, no two types of locks will be compatible, greatly increasing the chances for a deadlock.

What to Do
Now that we know that footprint is the biggest cause of deadlocks, how do we fix it? By minimizing the number of locks and the duration each of these locks is held. To do that, first we need to identify the queries involved in the deadlocks. The best way to do this is to turn on trace flags. When the deadlock trace flag is turned on, then a server-side trace is run which constantly monitors for deadlocks. When a deadlock is encountered, then the specifics of the deadlock are output to the SQL Server Error Log. Depending on which trace flag you implement, it will show you different things. My favorite trace flag is 1222, which was introduced in SQL 2005. It shows the two queries that were involved in the deadlock. The other trace flag (1204) will not show as much information however it is still useful. The following article will show the differences between the two trace flags.

The most typical things you need from the output is:

1) The queries involved
2) The time they happened
3) The objects the queries held

We can get into all the specifics regarding the output of the trace flag, however let me tell you this. 95% of the time it comes down to footprint and coincidence. And about 90% of that time this footprint and coincidence comes down to optimizing the queries shown in the deadlock trace. To do that, you will want to run the query and get the actual execution plan. NOT the estimated. The estimated is not a real representation, it’s only what the optimizer thinks it will do based on the statistics.

With this execution plan, the biggest thing you are looking for is large thick arrows for output.

Thick Arrows

These thick arrows represent a lot of rows being locked. Do not be fooled by index seeks. I have seen index seeks that output 70K rows. Always look for the thick arrows. This will cover you in the footprint category. Now for the coincidence category, if you can decrease the number of rows being locked, then you will likely also decrease the coincidence.

Deadlocks at a certain time of day
I’ve also seen deadlocks on queries that run all day long just fine, however at a certain time of day, they always deadlock. In my experience this is because intense processes were running at this time. When that happened, all the data was flushed from the buffer cache and SQL had to go to the disk in order to retrieve the data. When this happened, the footprint was increased because the time it took to read all the data. The solution is to make sure the page life expectancy does not fall low by either optimizing the queries run at that time, or increasing the RAM (but don’t tell your DBA I said that :) )

An Outlier
I did recently fix a strange deadlock occurring on a SQL 2008 R2 instance. What was strange is that INSERTs were deadlocking on each other. And the INSERTs were not the INSERT..SELECT type, they were the INSERT..VALUES type. In other words, they had a very small footprint. At the time I could only think of two scenarios that was causing this, lock escalation or that these inserts were waiting behind something blocking, then when freed up they collided. The deadlock graph confused things even more. The partition it showed being held by an X lock was partition: 0, however the table did have a clustered index and was not a heap. Further investigation showed that the partition it was referencing was actually part of a new type of partition introduced in 2005 used for a feature called “lock partitioning“. The problem turned out to be that one of the indexes did not have the options of “use row locks” or “use page locks” selected. Because of this, the insert (which also needs to update the indexes) had to utilize a table lock. When it did, the X lock (from two separate inserts) against the lock partitions collided because they seemingly did not lock the partitions in the same order every time. (Which I wonder if this is a bug). The fix for the deadlock was simply to enable the page & row locks on the index.

I do hope this helps.

Filed under Concurrency, SQL Optimization, Troubleshooting.
2 Comments

Comparing Query Performance

Posted by on 16 August 2011

If you write TSQL then comparing the performance of two queries will probably be something you do on a daily basis. The difficult part of comparing queries is getting an accurate baseline. Why is this you ask? Because SQL Server has behind the scenes functionality that optimizes queries for multiple executions. This allows minimal resources the second time a query is run. This is why often times, you will notice the second run can be considerably faster than the first.

There are a few features that make this happen.

Plan Caching

Immediately prior to the execution of a query, SQL Server needs to determine the best route to take for the query. This includes the indexes to use, the order of the tables to reference, the types of join algorithms to perform, and many other factors. Finding this route is a rather expensive process, so instead of performing this every time a query is ran, SQL Server caches the plan in memory so it can be reused. Naturally, the second time the query is run, it will be quicker because it does not have to determine the execution plan.

There are two ways to clear a plan cache. You can nuke all plans on the instance using DBCC FREEPROCCACHE with no parameters. Or you can pass in the parameter of the plan handle to clear.

Just running the following command will clear the plan cache on the entire instance

-- Clear the instance
DBCC FREEPROCCACHE

This will clear the plan cache for a particular query. Note that the plan cache DMV will store the entire query text, including comments, so if you execute the query with a bizarre comment, you can find the query using the bizarre comment.

-- Run the query
SELECT top 10 * --juggernaught
FROM Person.Address

-- Retrive the plan Handle
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%juggernaught%';

-- Clear the Plan cache for this handle
DBCC FREEPROCCACHE (0x06001D00663FE80140E1DFDE000000000000000000000000)

Finding the Plan Handle

Buffer Caching

The buffer cache stores the table data retrieved from physical disk into memory. The buffer cache typically saves far more time on an execution when compared to the plan cache. Where the plan cache will save somewhere between 5 – 400 milliseconds, the reuse of the data stored in the buffer cache can sometimes shave off many seconds. This is why a lot of times when comparing queries, I will only reset the buffer cache unless the query is going to have many executions / sec.

To drop the buffer cache is not pretty. You have to drop all the buffer cache memory for the instance of the SQL Server. Do not do this in prod, unless you want your disk to be overwhelmed.

-- Drop all the buffers
DBCC DROPCLEANBUFFERS

There, that command will level the playing field when comparing two different queries.

Network Traffic

If you compare your queries according to the run-time you see at the bottom right of the screen, then you are looking at the client-side time. This is the time it takes your client to receive the data. Though it is usually not significant, sometimes network traffic can have strange effects when trying to derive a baseline. This is why when comparing queries, I like to turn on the server-statistics an compare them.

To do this simply run

SET STATISTICS TIME ON

Once turned on, it will be on for the entire session (or until you close the window). To view these statistics, run the query, then click on the “Messages” tab in the result pane. There you will find the times for compilation, and the execution times for each batch.

Results from server-side time statistics

Disk Usage

Along with server-side time statistics, it is also beneficial to pay attention to the server-side IO statistics. These are turned on almost the same as turning on the time statistics.

SET STATISTICS IO ON

Once on, the “messages” tab will show the IO stats for the query. The one thing to pay particular attention to is physical and logical reads. Physical reads mean the hard disk is being accessed, while logical reads mean the buffer cache is being accessed. You can level the playing field by freeing the buffer cache using the method above.

Filed under SQL Optimization.
2 Comments

SQL Server Denali – Features Update

Posted by on 12 August 2011

After attending a meeting with a Microsoft engineer I have some more good news on Denali. Unfortunately the meeting ran short and we didn’t get through all the content so below I could not extrapolate on some points. I still listed them all below.

Performance Datawarehouse This has existed in SQL Server 2008, however now it comes with a wizard and is much easier to setup. Basically you install a new SQL Server and run through a wizard (using a domain account) to implement the Performance Data warehouse simply by selecting the remote boxes you want to monitor. The ETL is automatically done for you and collects data on a 15 minute interval. Very cool.
CodeName “Juneau” New SQL Server Developer tools integrated with Visual Studio. At the time of this writing, it’s on version CTP3. This tool looks excellent. It promises to make database development much easier and wrapped in source control. It’s easy integration with Azure seems like it will make Azure more attractive. You can view videos here: http://msdn.microsoft.com/en-us/data/hh297028. Download here.
Filetable Store files (ppt, word) and link them into sql server. Get transactional support for accessing files. You can Drag drop files into a directory, and then dynamically access them via SQL Server. When files are added, searches can be run against the documents using Full Text Search.
Semantic search within FT An optional parameter that captures the top 100 statistically relevant words from a document. Also captures the top 10 similar documents based on the document that is found, you can find more details here.
Faster Spacial Performance Provided parity between the geometry and geography type. Improved the CLR performance of the CLR objects and spacial index handling. New default index mechanism which gives more predictability for spacial operations. Nearest neighbor queries now take seconds to sub seconds.
Full globe spacial support New spacial objects including circle and arc to represent objects that span more than one Hemisphere. All spacial features can be found
here.
Sequences A sequence is an object that can be created on the fly that essentially provides an identity key. More details
here.
Paging for Result Sets Result sets can now be paged by using the OFFSET and FETCH parameters for the ORDER BY clause. Offset specifies the starting row number, where FETCH provides the number of rows. Both these parameters would need to be passed in via a stored procedure.
Local DB Runtime I believe this is to help support Azure development. If you are not connected to the cloud, you can still develop. Here is the MSDN article.
Partitioning The Enterprise Edition of SQL Server can now support 15K partitions per table. The previous limitation was 1000 partitions per table.
Column Store Index codename “Apollo” Creates an index on existing table and stores the column data together rather than the row data together. They report speeds increases of 100X for certain queries. This new feature uses the same technology built into Analysis Services that allows large amounts of data to be sliced & diced. Susan Price from the SQL Server Database Engine explains that this enables much better compression because there are more similarities for the data stored in columns rather than for rows. It’s main use seems to be to speed up queries typically used within a Data Warehouse (Star joins, aggregations, filtering). Since data warehouse queries only touch 10-15% of the columns in a source table, this translates into a big speed increase not having to pull all row data. Hear if from the source here. Also watch an example presentationhere (If you don’t mind Non-Ansi queries)
Faster full text search Full text search has been optimized to be quicker by implementing parallelism. Their goal was to support 100 million large documents and up to about 350 million small documents.
User defined Server Roles Server level roles can be custom defined… YAAAA! Finally :)
Contained Databases Option When a database is specified as “contained”, then it does not rely on the server-level (or instance) settings of the SQL Server in order to operate. Therefore it can be easily moved. This includes all settings including Security. Article here
Distributed Replay Save and replay sql statements from client machines in a particular order, oryou can run asynchronously. If you do run asynchronously, the order cannot be guaranteed. MDSN Article.
Audit Enhancements Can be secure to be untouchable by DBA’s and also go to a centralized location.
SQL Server Always On Formerly referenced as HADR (High Availability Disaster Recovery). This feature can be thought of as a combination of Log Shipping and database mirroring. It centers around an applications needs rather than specific databases. Meaning that if one database fails, then all databases associated to the failover group (or application) will be repointed. It also has the same synchronization modes as database mirroring allowing for transaction to be committed on up to two secondaries prior to being committed (synchronous) or allowing transactions to be committed on a primary group then distributed and later committed on secondaries. Secondary servers are also Active (readable) without being in standby mode for the log to recover.
Codename “crescent” A business analyst tool that’s part of reporting services allowing the user to create rich visuals to help describe source data.
More information.
Azure Enhancements Each SQL Azure DB has 3 replicas providing automatic replication and failover. MS is banking on Azure it seems. Of the 1200 people on sql server dev team.. half of those are on SQL Azure. It still has a 10GB limit currently.

On the Horizon

Two indications of what may be in the version following Denali are:

  • Enterprise Metadata – Likely will be integrated with Master Data Services.
  • Federation – The ability to scale out SQL Servers (multiple servers) rather than scaling up. I’m banking this will be an extension of their SQL Server Always On technology since the groundwork is already there. This will allow much larger throughput with much lower hardware costs.

More Denali items I do not have specifics on. Some of these can be found in the features pack.

  • php driver
  • odbc for linux
  • utf – 16 support
  • jdbc 4.0 driver
  • tsql & clr enhancements
  • dac enhancements
  • win32 access to database files
  • Management pack for HA
Filed under SQL Server Denali.
Comment

Dedicated SQL Server Hosting

Posted by on 16 July 2011

Want dedicated SQL Server hosting? The following guide may help in evaluating SQL Server Hosts.

SQL Server Azure – SQL Server Azure is the Microsoft SQL Server Cloud computing solution that allows you to remotely administer SQL Server via you SQL Server Management Studio. While it is difficult to figure the pricing model, it is obvious it is competitive, as these are the times to gather as many clients as possible. This network also makes use of a CDN (Content Delivery Network), and you can use SQL Server Management Studio to administer your site.

iWeb – This is the host I use. Yes I use them for WordPress hosting. Why do I host a SQL Server site on WordPress? Basically because I believe in using the right tool for the job. What I have to say about this host, is that they are one of the most reliable hosts according to netcraft that there is.

According to Netcraft iNetU is one of the most dependable SQL Server Hosts there is. The following link is from May 2010, however they have been reliable even since then.

Rackspace is an undeniable source of SQL Server hosting. While their prices are expensive, their support is extensive and they have superior customer service. Click here check out their SQL Server hosting.

Filed under Hosting.
2 Comments

Audit Tables Using XML Schema

Posted by on 16 July 2011

Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table.

The following trigger template can be used to audit a table into a single table.

First let’s create the table needed to hold the audited values:


CREATE TABLE [Log].[AuditTableHistory](
	[HistoryID] [int] IDENTITY(1,1) NOT NULL,
	[TableSchema] [varchar](10) NULL,
	[TableName] [varchar](100) NULL,
	[AuditValue] [xml] NULL,
	[DateCreated] [datetime] NULL,
 CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED
(
	[HistoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

-- Now let's create the trigger

CREATE TRIGGER [Audit].[Trg_Schema_Table]
   ON  [Schema].[Table]
   AFTER UPDATE,DELETE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @AuditValue xml

	SET @AuditValue =
	(
		SELECT TOP 1 *
		FROM DELETED
		FOR XML AUTO
	)

	INSERT INTO Log.AuditTableHistory
	(
		AuditValue,
		TableSchema,
		TableName,
		DateCreated
	)
	SELECT
		AuditValue	= @AuditValue,
		TableSchema	= OBJECT_SCHEMA_NAME(parent_obj),
		TableName	= OBJECT_NAME(parent_obj),
		DateCreated	= GETDATE()

	FROM sysobjects so
	WHERE so.id = @@PROCID

END

GO

We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so we can query the table appropriately.

Filed under Auditing, XML.
Comment

When to use the XML Datatype

Posted by on 26 May 2011

When I first heard of the XML Datatype, I had mixed feelings. The reason is because from a traditional approach, all data structures can be represented using the classical entity relationship model. For me, a problem arises when trying to represent “documents”. When I say documents, I don’t just mean things like “Word Docs”. What I mean by that is data structures that mean nothing when the data is broken up into segments, however when represented in a whole document, that’s where the only relevance comes in.

An example of this is a contract. It is possible to take disparate pieces of a contract and break it into separate rows and columns according to the contract terms. However, when you break a contract up into individual pieces, it means nothing when it’s separate. It’s context is only meaningful when the pieces of the contract are together. This is an example of when to use the XML Datatype.

I recently wrote a rules engine, where I created all the rules using XML. When I showed some people, their immediate reaction was, “why can’t we break these individual parts of the rule into a table”. My answer was, “because separated, these individual rule items mean nothing, there is no reason to separate them. They only mean something when they are put together because that defines the rule”. In other words, the entire rule is atomic, there is no reason whatsoever to break it apart. When it is separate it has no meaning.

More and more I am seeing instances where this has relevance. Ask yourself, does it make sense to break this data up? Will it ever have relevance when it is separated? If not, then the XML Datatype may be appropriate for you.

Filed under XML.
1 Comment

Find Resource Usage by Application

Posted by on 26 April 2011

Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful.

Here we will query the aggregate connection information. The values below represent an aggregate value of all connections that are currently established by that application.


SELECT
	 CPU			= SUM(cpu_time)
	,WaitTime		= SUM(total_scheduled_time)
	,ElapsedTime	= SUM(total_elapsed_time)
	,Reads			= SUM(num_reads)
	,Writes			= SUM(num_writes)
	,Connections	= COUNT(1)
	,Program		= program_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
	ON ses.session_id = con.session_id
GROUP BY program_name
ORDER BY cpu DESC

Note that the above query does not break down the values per user. In order to do that, we need to create an additional grouping condition (login_name).

SELECT
	 CPU			= SUM(cpu_time)
	,WaitTime		= SUM(total_scheduled_time)
	,ElapsedTime	= SUM(total_elapsed_time)
	,Reads			= SUM(num_reads)
	,Writes			= SUM(num_writes)
	,Connections	= COUNT(1)
	,Program		= program_name
	,LoginName		= ses.login_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
	ON ses.session_id = con.session_id
GROUP BY program_name, ses.login_name
ORDER BY cpu DESC
Filed under DMV Queries.
2 Comments
« Older Entries
Newer Entries »