Aleksandar's computer workshop

Let's see what Aleksandar was fixing today.
My findings, tips & tricks related to computers, internet, programming and other stuff I was working with.

SQLIO Disk Subsystem Benchmark Tool

When you notice that your Microsoft SQL Server starts slowing down and not responding quickly to your queries probably you will have to check your hard disk subsystem.

If you are not sure how much I/O operations your disk subsystem can handle you should try this tool.

SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration.

Golden performance tuning tip for SQL Server high CPU usage

I was busy today with one SQL server 2000. Problem was constant 100% CPU usage for long period several hours.

In normal situation customers schedules data for processing with maximum of 500-700k records in queue table with maximum CPU usage of 25-30%. Today queue went above 1M records. SQL server is hosted on dedicated high end class piece of hardware, with previous hardware and software performance tweaks. Good resource

Recently we have changed some part of the code so I decided to check what went wrong.

One thing I have noticed is many active connection (above 500). It was normal behavior, clients were busy today ;-).

I turned on the performance monitor which already has performance counter profile for monitoring which includes following:
  • Pages/sec
  • % Disk Time
  • Current Disk Queue Lenght
  • Disk Reads/sec
  • Disk Writes/sec
  • % Processor time
  • Batch Requests/sec
  • SQL Compilations/sec
  • SQL Re-Compilations/sec
  • Context Switches/sec

All values were in normal ranges except % Processor time (98-100) and Context Switches/sec (>8000).

I have checked general hardware health, RAID, HDD fragmentation, memory usage etc.. but everything was just fine.

Then I got idea to turn on Fiber mode (also called Lightweight pooling) for test, how it will react but I could recall that this option was tested before and that we had all kind of problems with SQL server. One of the problems was that sometimes SQL server did not wanted to start at all! Last thing you could see in SQL Server Log is this line:

"Recovery is checkpointing database ...."

After turning off the fiber mode SQL server would start properly.

Here is very nice article about SQL Server fiber mode:

Ken Henderson explains the effects that SQL Server fiber mode coupled with the User Mode Scheduler can have on your system, and what to consider carefully before enabling fiber mode

He's conclusion is:

Fiber mode was intended for niche situations in which a scalability ceiling is
hit due to UMS workers spending significant amounts of time switching between
thread contexts, or switching the CPU into kernel mode and back again. Unless
you have encountered this yourself, and have already tuned the system as much as
possible using more obvious (and safer) techniques, I recommend that you stay
away from fiber mode and focus your tuning efforts on other things. If you run
into a situation where you feel strongly you need fiber mode, it's probably
worth a call into Microsoft Product Support Services to confirm your diagnosis.
Better to get a second opinion than to break your server in subtle and
pernicious ways with little hope of determining how or why, and with no likely
improvement in overall performance.

So fiber mode was not an option. Hardware was working properly, but all 4 CPU's were still on 100% usage.

Then I turned on SQL Profiler. After 1h of data collection I had enough material to work with.

After running "Index Tuning Wizard" several times I got the results. 2 new indexes on one table should improve performance up to 77%!

CPU usage dropped from 98-100% to 3-5%. After 1/2h queue was reduced for almost 200k records!

My golden tip would be:

When you want to boost performance of your SQL server first thing to check are indexes!

Optimizing SQL Server Query Performance

When optimizing your database server, you need to tune the performance of individual queries. This is as important as—perhaps even more important than—tuning other aspects of your server installation, that affect performance, such as hardware and software configurations.

Even if your database server runs on the most powerful hardware available, its performance can be negatively affected by a handful of misbehaving queries. In fact, even one bad query, sometimes called a "runaway query," can cause serious performance issues for your database.

Conversely, the overall performance of your database can be greatly improved by tuning a set of most expensive or most often executed queries. In this article, I will look at some of the techniques you can employ to identify and tune the most expensive and worst performing queries on your server.

* Analyzing execution plans
* Optimizing queries
* Estimated Cost of Execution
* Analyzing an Execution Plan
* The Covering Index
* Indexed Views
* Identifying Which Queries to Tune
* Customer Order Query Exercise

Properly Capturing Identity Values

When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?

SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
3) IDENT_CURRENT(‘tablename’)

But which should you use? Check out this article for more information: