Featured Article(s)
Tips for using SQL Server 2005 Database Console Commands (Part 3)
In this article, Alexander Chigrik shows how you can optimize SQL Server 2005 performance by using database console commands.
Cool Contest
Have you seen this? They’re holding a contest looking for the biggest, baddest DTS package – and they even have a prize every month. Pretty cool. Do you have the biggest DTS package going? Drop by the site and get more information – but it’s a great idea for a contest. Take a look.
Webcast – Available Now
Understanding Key Fundamentals of SANs and SQL Server
Learn about some key returns on investment that you can expect with SANs, along with best practices, tips for best usage and how SQL Server and SANs can provide a compelling environment both in terms of availability and performance.
> Watch Webcast
SQL Server, Table Locks and BI
It’s kind of odd how things go in cycles when it comes to the types of questions we see here at SSWUG. One of the things that’s been happening quite a bit lately revolves around locking/blocking behavior and certain BI processes.
I’ve been seeing more issues surrounding this – essentially "the report ran fine before, but now it just sits there and spins." Of course the question arise, "what changed?" and the answer… "nothing."
Well, what we’re seeing change are the queries running against the system. These queries are *trying* to get more information out of the system and provide it for BI-oriented solutions and/or the latest brainchild of the developers or DBA.
The key? The queries are trying to summarize data, make calculations or do other things that SQL Server has to have the data in a known state to accommodate. "Known state" also means "not changing" – so SQL Server locks tables in some of these cases. In one example, a popular products query was running. No problem, except that the query had to total up orders, get quantities sold, then sort based on that information. Of course the calculations can put other things on hold while the counts are done, sorts completed, etc.
So, the very short moral to the story is that if you’re seeing locking/blocking activity, check to see if perhaps you have some over-zealous queries running. These could be jobs, could be reports, could be outright TSQL queries. Each has the potential. If you do see this, consider making a summary table that has the calculations already completed – then point your BI queries at that table, rather than the detail rows and tables. It can work wonders for the performance of your application and in preventing the locking/blocking activity.
Featured White Paper(s)
SQL Server Virtualization Management
Virtualization is becoming the popular practice in many environments, allowing organizations of all sizes to utilize resource… (read more)