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 [...]
Archive of entries posted by
Execution Plans
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 [...]
Understanding Batch Requests / sec
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 [...]
Understanding SQL Server Deadlocks
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 [...]
Comparing Query Performance
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 [...]
SQL Server Denali – Features Update
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 [...]
Dedicated SQL Server Hosting
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 [...]
Audit Tables Using XML Schema
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 [...]
When to use the XML Datatype
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 [...]
Find Resource Usage by Application
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 [...]
