Professional SQL Server 2008 Internals and Troubleshooting

Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort

Mentioned 6

A hands-on resource for SQL Server 2008 troubleshooting methods and tools SQL Server administrators need to ensure that SQL Server remains running 24/7. Authored by leading SQL Server experts and MVPs, this book provides in-depth coverage of best practices based on a deep understanding of the internals of both SQL Server and the Windows operating system. You'll get a thorough look at the SQL Server database architecture and internals as well as Windows OS internals so that you can approach troubleshooting with a solid grasp of the total processing environment. Armed with this comprehensive understanding, readers will then learn how to use a suite of tools for troubleshooting performance problems whether they originate on the database server or operating system side. Topics Covered: SQL Server Architecture Understanding Memory SQL Server Waits and Extended Events Working with Storage CPU and Query Processing Locking and Latches Knowing Tempdb Defining Your Approach To Troubleshooting Viewing Server Performance with PerfMon and the PAL Tool Tracing SQL Server with SQL Trace and Profiler Consolidating Data Collection with SQLDiag and the PerfStats Script Introducing RML Utilities for Stress Testing and Trace File Analysis Bringing It All Together with SQL Nexus Using Management Studio Reports and the Performance Dashboard Using SQL Server Management Data Warehouse Shortcuts to Efficient Data Collection and Quick Analysis Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

More on Amazon.com

Mentioned in questions and answers.

Are there any tools that do Query Optimizations for SQL Server 2005 and above?

I've searched & didn't find anything.

What do you recommend?

If this Question was repeated before you can close it but I didn't see anything similar

As John Saunders commented, the best tool at your disposal is your own mind. Following bernd_k's comment, here are a few suggestions for sharpening that tool.

I need to know about the lock concept in sqlserver why it is used ? and with any example .is there any pdf or ppt pls specify that

In my opinion this is too broad a topic to cover in a post.

However to get you started on your learning I would recommend reading through the relevant content in SQL Server Books Online as Martin Smith suggests in the comments.

If you want to get hands on, an excellent book that has a great Chapter that covers the concepts that are involved in the subject of locks (as well as latches) is Professional SQL Server 2008 Internals and Troubleshooting.

I can't speak highly enough about this title (and no I'm not on the payroll :-) ) It's one of those books that you will always want to keep close to hand so you may return to it when you want to look up something or refresh you memory on a detailed topic, such as SAN storage considerations for the DBA, How does the query processor work, How does SQL Server manage Memory or what Performance Counters you should be monitoring.

I've been developing and administering Oracle database apps for many years, and would like to learn SQL Server. Does anyone have any book recommendations (preferably electronic). Naturally enough I already know SQL inside/out so I'd like to avoid any intro stuff and go straight to the meat, but not skip any basic SQL Server things I'd learn from an intro book. Any suggestions?

tia

I would definitely recommend:

If you have more of a development focus:

There are also some very good, free electronic books from Redgate:

to list a few.

I'm looking for a good book for SQL server optimization and query tuning.I thought about 'Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook by Ken England' or 'Microsoft SQL Server 2000 Performance Tuning Technical Reference by Edward Whalen' ,anyone have some advise ? (if there is also another good and interesting book I would like to hear about)

Tx.

I seem to be missing something wrt locks in SQL Server 2008. This is my scenario:

  1. Begin tran.
  2. Read from table A to ensure a particular row is found.
  3. While reading, place a read-only lock on the single row read. If not found, throw error.
  4. Insert into table B, which includes a reference to table A.
  5. Commit the tran (releasing the lock).

Due to various design constraints, in this particular instance I cannot create a relationship to manage this for me. So I have to do it with code.

I don't want to XLOCK or UPDLOCK table A as the transaction I am in is only reading from it, not writing. However, obviously I don't want anything else to update / delete the referenced row either. So I need a read-only lock from an external perspective.

I don't want any phantom reads to be possible. I don't want different row versions to be possible, obviously.

Once the tran has committed, it's fine for table A to be modified because a trigger (after delete) will null the reference in table B.

This is what I have:

BEGIN TRAN
-- test
IF NOT EXISTS (
  SELECT  1
  FROM    Table1
  WITH    (HOLDLOCK, ROWLOCK)
  WHERE   (ID = @ID)
  ) {throw}

  {perform insert into Table2}
COMMIT TRAN

Set your transaction isolation level to REPEATABLE READ for the duration of your transaction. This is also preferable, in my opinion, to using locking HINTS because of the increased clarity of your code implementation.

As you are reading only a single row, you do not need to worry about range inserts to your set, a caveat of REPEATABLE READ.

I suggest reading the "Locking and Latches" chapter in the book Professional SQL Server 2008 Internals and Troubleshooting. It contains excellent explanations, including code examples, of the various Isolation Levels available in SQL Server as well as describing the mechanics of the each of the data anomaly scenarios, such as Phantoms etc.

DISCLAIMER: I'm not on the payroll for this title but I do have two copies, one hard and another on kindle edition, that's how good it is that I bought it twice!

I was wondering if some tips or guidance can be provided for the following issue.

Environment type: Small office, approx 20 users. Network: All server, workstation belong to a single subnet Infrastructure: Virtualised webserver and virtualised database server.

Currently I have a web server (Win7SP1 x64) running Apache 2.2.22 (Win32) and PHP 5.3.10. I have built some pages that connects to our database (SQL2k8 Express) and calls procedures, the procedures are basically just basic select statements with joins and conditions. Once the result set are returned, PHP loop through the result set, row by row and display the rows as it flow through.

What happens is that from time to time, the result page can take a extremely long to load,where no results set are displayed but the web browser loading bar/icon flashes. Some times it will just give a blank screen at the end of the load, which typically means the query connection has timed-out or some times it will return the result set. For example, under normal behaviour, a page will take approx 1-3s to load, while at odd times, it may take up to 20-30s.

I have noticed that if I run queries in SQLSVR Mgmt Studio, queries that may take ~5s to return a result set may take approx ~15s to load on my web server.

I know poor PHP coding can cause slow downs but the odd performance behaviour has me bit baffled, as Im sure my coding isnt that bad.

So if people could offer some tips in how I can go about diagnosting or isolating the problem, or advise areas that I could look at, it will be great. I know there are Apache logs, SQL SVR logs and performance indicators, but Im still fairly new to this area and am really lost in the approach /methodology I should take.

Example, any specific mods I should enable in Apache etc. or the type of Apache server I should use, connection sockets in web server etc?

Example of my PHP query/procedure calls

$queryStatement = "exec myProc @para1='asd'";
$prepare = $dbConnect->prepare($queryStatement);
$result = $prepare->execute();
WHILE($result = $prepare->fetch(PDO::FETCH_ASSOC)){
set_time_limit(180);
echo $result
}

Cheers everyone :)

I recommend you do some reading on how to approach SQL Server performance troubleshooting. The Waits and Queues methodology is a good start, but if you're absolute beginner it may be a little bit too abstract. There is also Troubleshooting Performance Problems in SQL Server article, which is a more hands-on approach into identifying the bottleneck(s). The SQL Server Customer Advisory Team has put together a Performance Troubleshooting Flowchart that can help. And there are many good books, like Professional SQL Server 2008 Internals and Troubleshooting or Troubleshooting SQL Server - A Guide for the Accidental DBA (there are many more).

Some possible causes that would cause symptoms similar to what you describe:

  • a bad query plan. Sometimes you get a good plan that executes in 1-2 seconds, but sometimes you get a bad plan that takes 30 seconds. This issue is discussed at length in Slow in the Application, Fast in SSMS? Understanding Performance Mysteries and investigation requires capturing and comparing execution plans. However, the symptoms would be that the problem page is responsive and then, all of the sudden, becomes unresponsive with 30 seconds load times, stays unresponsive for a while, then mysteriously becomes responsive again.
  • lock contention on updates. This would mean that your reads are blocked behind writes waiting to commit. This would cause sporadic single-time, odd, unresponsive pages. But 30 seconds loads would imply a 30 second write, which is unlikely.
  • Database growth events, specially log growth events. This one would cause exactly the symptoms you describe: odd, one time, unresponsive pages taking 30-40 seconds to load. Also, when this happens, any page on your site would take 30-40 seconds to load, and then they would all become again responsive. This is explained by your database files (Specially the log file) having to grow, and the 30-40 seconds is the time needed to expand the file and initialize it with zero-s. Fortunately this is very easy to investigate: the growth events are visible in performance counters SQL Server, Databases Object Log Growths: Total number of times the transaction log for the database has been expanded. If this counter is non-zero fro your database, is likely the culprit. Data file growths unfortunately are not captured there, but they are captured in the default trace (the linked article shows how to read it). Note that log growths also reveal that you have a non-simple recovery model set and no log backups occur, your log may grow until it fills the entire disk... Easy to validate using sys.database_files.

These are just some basic ideas barely skimming the surface of what you need to look at... unfortunately there is no silver bullet.