Call us Today! 201-728-8809 |
Barcode Tracking Products
Barcode File Tracker
Barcode Medical Chart Tracker
Traverse Barcode Asset Tracking Software
Traverse Barcode Inventory Tracking Software
Barcode Point of Care
Barcode Point of Care Hospital / Nursing Homes
Barcode Point of Care Schools
VICS Bill of Lading
At a Glance
SQL Database Health System
SQL Database Health Monitor
SQL Database Data Cleaner
SQL Backup and Copy and FTP
Full Disk? Find Largest Directory
Dynamic DNS Utility
Analysis and Design
Scope Creep Mitigation
Database Troubleshooting and Tuning Series
Are your developers hindering database performance? Part IV
July 2012. Volume 1 - Issue 4
By Tom Romeo
The goal of this article is to help developers design applications with consideration for the importance of proper database interaction for maximum performance.
To Profile or not to Profile
In our previous article we demonstrated how Disk or CPU can be the bottle neck if an index is not properly constructed or if a query is not formed well to use the desired index.
To recap, we illustrated how Select * is a bad practice. Not only did it bloat the result set, it caused the query plan to perform table scans instead of index seeks.
After we have tackled all of the items on our list of obvious offenders, that is queries that have a high value for max_elapsed_time as well as min_elapsed_time, and last_elapsed_time, we may now look deeper at queries that normally run reasonably fast, but occasionally run very slowly. These queries may slow down sometimes due to resource contention, or because locks and or deadlocks are occurring. "SQL Server Profiler" may become your knight in shining armor when this occurs.
SQL Server applies locks via complex algorithms and usually does a very good job of it. Occasionally, nolock or rowlock hints can be used to help, but these hints should be used sparingly, and rowlock, for example is not always acknowledged or obeyed. Actually nolock is among the group of settings that has been deprecated.
Typically I set these SQL trace flags
dbcc traceon (1224, -1) -- Disables Lock escalation unless memory pressure forces it
dbcc traceon (1204, -1) -- Returns resources and types of locks
dbcc traceon (1222, -1) -- Returns deadlock information
MSDN - DBCC Trace Flags
To guarantee that they are always set, I create a stored procedure in the master database that sets the flags and then use "exec sp_procoption", setting startup true. This way if SQL Server is restarted or if the server is rebooted, the flags are re-set.
MSDN - sp_Procoption - setting SQL startup processes
if you are a bit squeamish about using the SQL Server Profiler, you could try this approach to find out which objects are locking out others. If however you do not find satisfaction here, it is time to dig in.
MSDN - Determine Which Queries Are Holding Locks
Starting a SQL Profile Session
From your start menu where you would normally open SQL Management Studio, there is a Performance Tools menu. The SQL Server Profiler is listed under the Performance Tools menu.
Open the SQL Server Profiler.
If you will be performing a lot of SQL traces then making your own trace template will save you time.
From the File menu, create a new trace. After entering database connection credentials you will see a screen like the following:
Enter a trace name. Microsoft provides several templates with preselected options. If you wish to re-run your test over and over you could start with the TSQL_Replay template, but you may also need to have a good backup of your starting point so you can restore before running again.
Select the save to file check box and select a location for the file to be created. It really depends on how much data you will be collecting, but I usually set my maximum file size to 200MB and enable file rollover. Notice I selected a blank template because I have a specific set of data that I want to collect.
Click the Events Selection tab. Generally I select every error because if any errors are occurring within my application I would like to correct them.
I usually select every type of lock event as well. Notice when you select the deadlock graph event a new tab entitled "Events Extraction Settings" appears.
We will also collect:
Show Plan All
Show Plan XML
On the events extraction settings you can select XML Showplan and Deadlock XML. It is best to capture each deadlock and each query plan into a separate file because it is easier to separate each plan as it is opened via SQL Management Studio and also, if the plan file is very large it can cause an out of memory exception in SQL Management Studio.
Please note that many SQL plans will be generated so it is best to put them in their own directory. Or you can bypass SQL plans all together as you can always see the plan by running each suspect query on its own.
You can use the column filters to single out a single database or several. If there are many Databases on your SQL server it is best to single out your database as the SQL trace will be more verbose than you probably expect.
Run the trace until your error condition occurs.
To illustrate, I ran a test such that I inserted data from two different web browsers at the same time.
When I began the test the database was effectively empty as it was a fresh installation of the software. I opened two instances of the web client software, one chrome, and one Mozilla Fire Fox. I logged in to the system from each browser as two different users. I opened the section to add data of a particular type. I clicked save from both browsers as simultaneously as I could. After about 30 seconds a deadlock occurred. The deadlock graph shows the two transactions that were in contention and the deadlock victim. The trace also shows the time to lock acquisition and deadlock exception. By tracing the transaction identifiers from start to finish it is clear that each transaction executed six separate SQL batches, between the begin transaction and the commit / rollback transaction. This means an ADO.NET begin transaction was issued by each save button click event followed by six separate SQL batches before an ADO.NET commit / rollback transaction was issued. Each transaction consisted of eight distinct round trip calls to the database.
Having worked on systems that can accommodate hundreds of concurrent users, I was shocked to discover that I could cause such an error so easily with just two users.
All of this is pictured below.
This illustrates one of the important points made in our first article entitled,
"A Guide to properly constructing database transactions"
DO NOT issue a begin transaction in a single batch and proceed to perform multiple additional SQL batches followed by a commit at the end. This will impose unnecessarily long locks causing others to wait for database resources. For example DO NOT DO THIS:
Open DB Connection
Execute ADO.NET "Begin Transaction"
Select * from tblFooBar"
Execute "Update tblFoo"
Execute "Update tblBar"
Execute "Delete tblFooAgain where ..."
Execute ADO.NET "Commit Transaction"
Close DB Connection
Each execution is a round trip to the database server. It is far better to send everything including and between the Open DB Connection and Close DB Connection in one round trip to the database server.
Deadlock Chain Discovered
Deadlock victim Discovered
Transaction ID's for the deadlocked pair with time to lock acquisition
Click here to see other articles in the series.
VICS Bill of Lading
QuikVixBOL ™ Overview
QuikVixBOL ™ Technical Details
QuikVixBOL ™ Success Stories
Barcode tracking solutions
← Traverse → ™ Overview
Bar-Code File Tracking System
Bar-Code Medical Chart Tracking System
Prevent Medication Errors with barcode technology
Barcode Point of Care for Schools
Barcode POC Overview
What Is My IP
DB Health Assistant
IT Consulting New York City Metro Area
Custom Software Solutions
Scope Creep Rescue
IT Expert Witness Services
Pragmatics - PCS eNewsLetter Articles
Database Performance Series - Proper SQL Transactions
Database Performance Series - Collecting Empirical Data
Database Performance Series - SQL Execution Plan Analysis
Database Performance Series - SQL Server Troubleshooting
Using GUIDs in the database
What is all this CRUD?
I thought my database was collation agnostic.
Free Stuff From PCS
Where Did My Disk Go Utility to find lost Disk space
Print a photo ID for your child
Math Hound - Help your children master basic arithmetic
True Cost Mortgage Calculator
True Cost Shows how much things actually cost
SimpleQ a Simple SQL Database Query Tool
Our About Page
Our Contact Page
Full Site Map
Practical Compliance Solutions
One Orient Way - Suite F156
Connect With Us
News and Articles
Database Performance Series - SQL Server Troubleshooting
© 2002 - 2018 - Copyright Practical Compliance Solutions - All rights reserved