|
|
|
|
Browse by Tags
All Tags » Performance » Best Practices (RSS)
-
What does it mean if you see a high percentage of signal waits? Thanks to Microsoft whitepapers, presentations, and blogs, everybody would say it implies CPU pressure. Well, almost everyone except Mario Broodbakker, whose excellent blog “ Taking the guesswork Read More...
|
-
A frequently asked question is what counters should be included in a SQL Server baseline. The discussion then quickly proceeds to define a set of perfmon counters to be logged as the performance baseline. And often, people seem to have an urge to try Read More...
|
-
To many, this is an old and tired topic, and any more mention of ad hoc queries versus parameterized queries may just send someone off the deep end. But recently I was doing some Oracle benchmarks, and the benchmark tool reported ~1,200 transactions per Read More...
|
-
Regardless of the DBMS make or model, the transaction throughput curve of a database system is often shaped like a trapezoid. As the load level goes up initially, so does the transaction throughput. As the load level continues to go up, the transaction Read More...
|
-
The best documentation on the I/O behavior of SQL Server checkpoints is found in SQL Server 2000 I/O Basics by Bob Dorr. In particular, you should read the following carefully: SQL Server uses the following steps to set up another page for flushing and Read More...
|
-
Within the SQL Server community, there is so much publicity on T-SQL set-oriented processing as good practice and the use of T-SQL cursors as bad practice that T-SQL cursors are effectively being seen as a plague to be avoided when in fact it's not the Read More...
|
-
In response to my previous blog post-- Performance Impact: Setting a Database to Read Only , Shailesh Khanal mentioned that he observed significant performance degradation from READ COMMITTED SNAPSHOT OFF to ON for a read-only workload. This is counter Read More...
|
-
With the insert script and the test configurations in my previous posts , the best data load throughput was 24GB in ~7 minutes when the checkpoint (and/or transaction commit) batch size was set to 100,000 ~ 1,000,000. That was the best result when I was Read More...
|
-
In my previous posts ( 1 , 2 , 3 ), I focused on the performance behavior of setting the checkpoints and transaction commit sizes to once every 16 inserts and once every 100,000 inserts. A question remains: what is the most optimal size? In other words, Read More...
|
-
In my two previous posts on the performance impact of frequent manual checkpoints and the I/O behavior of frequent manual checkpoints , I demonstrated that frequently issuing manual checkpoints can be bad for performance and why it's bad from the storage Read More...
|
-
In my previous blog post on the performance impact of frequent manual checkpoints , I highlighted the performance peril of going overboard with manual checkpoints, and I suggested that a major contributing factor was the failure of frequent manual checkpoints Read More...
|
-
Recently, I was asked why the following script took a very long time to insert 24GB of data into a single table in SQL Server 2005 (the database was in the simple recovery mode): -- Name: Script 1 SET NOCOUNT ON SET IMPLICIT_TRANSACTIONS ON DECLARE @i Read More...
|
-
My earlier attempt to see what's going on with trace flag 1118 on SQL Server 2005 SP2 (9.00.3042) didn't take me too far because the results were not conclusive. I was motivated by seemingly conflicting suggestions from KB article Q936185 and whitepaper Read More...
|
-
A recently published KB article Q936185 seems to contradict the recommendation of using trace flag 1118 and multiple tempdb data files with equal sizing to reduce the contention on tempdb object allocation in SQL Server 2005. The article states the following: Read More...
|
-
Adam Machanic asked me whether I had done any tests to compare the performance impact of using SQL Profiler on the client side and that of using SQL Trace on the server side. This is an interesting question because the frequently heard recommendation Read More...
|
|
|
|
|
|