Search results matching tags 'Internals', 'Performance', 'Administration', and 'Tips'

Learn More About SQL Server IO and Query Tuning in These Webcasts

KKline — Fri, 14 Dec 2012 18:50:00 GMT

I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.

SSDs are a Game Changer for SQL Server Storage

No, session is not exclusively about SSDs. But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks. As Bob Dylan said "Times, they are a'changin'". This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology. I'll show you how SQL Server handles storage structures, how to identify IO activity on Windows and SQL Server, and best practices for minimizing IO bottlenecks. Register now for: Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues.

Write Better SQL Queries

The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand (Twitter | Blog) and SQLCruise Impresario & Microsoft MVP Tim Ford (Twitter | Blog) as we take you through the query tuning process, discussing important DMVs to use during query tuning, as well as demonstrating several essential query tuning techniques that every SQL developer should know. Not only are we presenting an hour of top quality technical content, we’ll also be giving away some cool prizes, including the grand prize of a paid registration for the upcoming SQLCruise Miami, a $1,395 value! Register now for: SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford

I hope to see you at both of these sessions next week! Best regards,

-Kev

-Follow me on Twitter!

New on SQLMag.Com: Update to SP_WHOISACTIVE

KKline — Thu, 21 Apr 2011 14:12:00 GMT

I profiled Adam Machanic's (blog | twitter) excellent stored procedure, SP_WHOISACTIVE, back in August of 2010 in my monthly SQLMag column, Tool Time. Adam has been diligent about maintaining the tool and adding new features. Read the details on my SQLMag Tool Time column (here - http://www.sqlmag.com/blogs/tool-time/entryid/76341/new-release-of-sp_whoisactive).

Enjoy!

-Kev

Twitter at kekline

Old Performance Tuning Recommendations Die Hard

KKline — Tue, 23 Jun 2009 14:19:00 GMT

It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own. In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.

Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005. The document states:

Multiple tempDB Files for Better Scalability of XML Variables and Parameters

XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.

Example: Creating Multiple tempdb Files

This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.

Copy Code

USE TEMPDB

GO

ALTER DATABASE tempdb ADD FILE

   (NAME = 'Tempdb_Data1',

      FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),

   (NAME = 'Tempdb_Data2',

      FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB)

GO

ALTER DATABASE tempdb ADD log FILE

   (NAME = 'Tempdb_Log1',

      FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),

   (NAME = 'Tempdb_Log2',

      FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)

GO

These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.

There was a time when this recommendation made sense. The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios. Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file.

This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket. (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)

However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style. On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.

As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications. I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.

Cheers,

-Kev

Twitter @ kekline

P.S. Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.

P.P.S. I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0. ;^)