Part 1: Analyze SQL Profile traces with a SSRS dashboard.

This blogpost is the first of a serie, in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler trace files. SQL Profiler is a perfect tool you need to use to analyze the performance of your application. When you have a SQL Profiler trace file the challenge begins to find that part of your application where you can make significant improvements. Improvements can be made in different ways, you can focus on:

• CPU
• Reads
• Writes
• Number of queries
• Repeating queries.
• Applications
• No SQL time. (Time between end time of query X and start time of query X+1)

The big question to answer, what is the best to focus on. This can all be done with the SQL profiler itself however it takes a lot of manual work. With SQL Server Reporting Services I made a dashboard which can be used to analyze a SQL Profile trace file. The dashboard will help you to visualize the bottleneck and to some in to this bottleneck to get more details. This dashboard contains 13 SSRS reports in total.

Click on the picture to enlarge.

In this dashboard, you will get statistics for the selected time frame about:

Total number of queries
Total trace time
Total Duration (SQL time)

Maximum number of Reads,Writes, CPU, Time SQL
Average number of Reads,Writes, CPU, Time SQL
Sum of total Reads,Writes, CPU, Time SQL
Query TOP 5 Reads,Writes, CPU, Time SQL
Repeating Query TOP 25 Reads,Writes, CPU, Time SQL
TOP 25 Reads,Writes, CPU, Time SQL by application

You can use the filters in the report to analyze a specific time frame or to zoom in to a specific bottleneck. In future posts I will explain how to make this dashboard. Please let me know if this can be interesting for you to use.

Changes in support for SQL Server 2000 and SQL Server 2005.

There are some important support changes coming up for Microsoft SQL Server 2000 and SQL Server 2005. To avoid risk of running unsupported products, you need to choose the right version of SQL Server for your business.






What will change:

SQL Server 2000

On 4/9/2013, Extended Support for SQL Server 2000 will come to an end, and SQL Server 2000 will no longer be supported. After this date:

• Updates to this software will stop and so you will no longer receive patches including security updates.
• Self-Help Online Support will be available for a minimum of 12 months.

SQL Server 2005

On 4/12/2011, SQL Server 2005 will transition from Mainstream Support to Extended Support, which includes:

• Paid support (charged on an hourly basis per incident). Customers will no longer receive no-charge incident support and warranty claims, and won’t be able to request design changes or features. 
• Security update support at no additional cost.
• Non-security related hotfix support will require a separate Extended Hotfix Support Agreement to be purchased within 90 days of the end of Mainstream Support – July 11th, 2011.

More information about these support changes can be found here.
Contact your software vendor to ask if they sell the so called 'Runtime' license of SQL Server. A Runtime license is much cheaper than a full license (up to 40%). Both license versions have exactly the same functionality. There is only a difference in the applications you are going to use with SQL Server. The Microsoft SQL Server Runtime license allows a customer to use SQL Server only with the vendors applications. The customer is restricted from using this SQL Server software to run other applications or to develop new applications, databases, or tables. So if you uses your SQL server with only applications of software vendor X, you can buy a run time license from software vendor X.

Why is my TEMPDB database so big? Reasons of extreme growth.

Sometimes I got questions from people asking why the TEMPDB database is soo big, especially in comparison with the size of the user databases. Triggers can be the reason for this. For instance an update trigger in combination with a lot of updates. Row versioning is a general framework that is used to support triggers. Every change (old and new value) made by the trigger is stored in the tempdb database. Row versions are held in the tempdb version store for as long as an active transaction must access it. This means that long running transactions with a lot of updates will result in a lot of entries in the transaction version store. The content of the current version store is returned in sys.dm_tran_version_store.  You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. More information about how tempdb is used can be found here. Use the SQL Profiler to see which updates are executed and analyze if you can update these records in a more efficient way.

Guest lecture: Performance testing of data intensive software.

In august 2008 my company Exact started a strategic collaboration with Delft University of Technology. Exact and the TU Delft signed a long-term strategic collaboration agreement that will allow the two organizations to share their knowledge and expertise to develop new technologies, products and services. Today, I had the honor to give a guest lecture for the students of Arie van Deursen, a full professor in Software Engineering at Delft University of Technology, who is leading the Software Engineering Research Group. In the guest lecture I talked about the performance testing of your application. What kind of thinks you should take into account before you can start your performance tests.



If you are unable to see the presentation, please click here.

More details about concurrency, locking and blocking in SQL Server.

The performance of your application on a SQL Server database is for most customers xtremely important. In most situations applications from different providors will connect to the same database. For instance: Excel, SSRS, Crystal Reports, MRP Application X etc.. Connections to a database can be made in different ways:

1. Read Uncommitted
2. Read Ccommitted
3. Repeatable Read
4. Serializable
5. Read Commotted Snapshot
6. Snapshot Isolation

There is not one best isolation level to use. It depends on your application. If different application uses  different isolation levels to connect to the same database, it can result in unexpected lockings. Sunil Agarwal, Principal Program Manager in SQL Server Storage Engine Group, has made some great blog posts about these isolation levels. These blog posts will explain in detail the behaviour of every isolation level. Beside this he made some demo scripts to show lockings which can occur in situations you do not expect.

1. Basics of Transaction Isolation Levels
2. Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
3. Why do I get blocked when no one has locked the row(s) being queried?
4. Minimizing blocking between updaters.
5. My application was running fine yesterday but why is it blocking today?

The big question during the analyze of unexpected blocking issues in your database is which application is responsible for this. The first step in this analyze process is to understand which isolation levels are used by the different applications. In this blog post: "Bad performance and lockings occur random on my database."
you can find a query to retrieve the different isolation levels of the different applications which are connecting to your database.

Enjoy it and good luck in finding the root cause of the unexpected locking and blockings.