THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » Best Practices » Testing » Performance   (RSS)
Showing page 1 of 2 (17 total posts)
  • Performance impact: thread mode vs. fiber mode

    SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on May 4, 2009
  • Performance impact: a large number of virtual log files – Part II

    In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on February 12, 2009
  • Performance impact: a large number of virtual log files – Part I

    It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on February 9, 2009
  • Performance impact: file fragmentation and SAN – Part IV

    Lies, damned lies, and statistics!   If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on December 22, 2008
  • Performance Impact: Manual Checkpoints are not Necessarily Evil

    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 perspective. If you were led to believe that manual checkpoints were always bad, that wasn't ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 20, 2007
  • Performance Impact: Frequent Manual Checkpoints and Their I/O Behavior

    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 to take advantage of the throughput potential of the underlying storage. But I didn't ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 17, 2007
  • Performance Impact: Frequent Manual Checkpoints

    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 1SET NOCOUNT ON SET IMPLICIT_TRANSACTIONS ON DECLARE @i int SET @i = 1 WHILE @i <= 3145728 -- (1024*1024*3) BEGIN INSERT tbTest(i, ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 13, 2007
  • Reduce the Contention on tempdb with Trace Flag 1118: Take 2

    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 Working with tempdb in SQL Server 2005. The KB article lists the following as conditions that ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 10, 2007
  • Reduce the Contention on tempdb with Trace Flag 1118: To Enable, or Not to Enable?

    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: In Microsoft SQL Server 2005, you may notice blocking ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on August 7, 2007
  • Which is Faster -- (1) Copy and Local Bulk Insert or (2) Bulk Insert across Network?

      A while back, I was asked which of the following two bulk insert options would be faster: Copy/Bulk Insert: First copy the data file from server B to server A, and then bulk insert the data into a table on server A Bulk Insert across Network: Directly bulk insert the data from server B into the table on server A Intuitively, the ...
    Posted to Linchi Shea (Weblog) by Linchi Shea on July 23, 2007
1 2 Next >
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement