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

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!

Microsoft Windows Platforms Blog Watch

KKline — Wed, 20 Jul 2011 14:44:00 GMT

Remote Desktop Services Component Architecture Poster

Grab your own poster! A visual guide to key Remote Desktop Services technologies in Windows Server 2008R2

Virtually Free

Get the latest update rollup package for the Hyper-V role in Windows Server 2008 R2 and be sure to bookmark the Windows Virtualization Team Blog. Be sure to check out blog World Simplified is a Virtual World. And doncha evah neglect application virtualization, such as the goodness at the App-V Product Team Blog.

Let's Optimize Some Desktops (Assuming You Have Gone Full Cloud Yet)

Check out The Official Microsoft Desktop Optimization Pack (MDOP) Blog where you can get cool tools like the Diagnostics and Recovery Toolset (DART), currently in its v7 beta release.

We Can Always Perform a Little Better

Have you checked out the Ask the Performance Team blog yet. You should! Then there's the Microsoft Enterprise Networking Team blog. And if you're looking for help with the Windows Server Core, be sure to check out the Ask the Core Team blog. Excellent stuff! As the Tool Time columnist at SQL Server Magazine, I'm always on the lookout for great free tools that get on-going support from their creators. One common experience is finding a nice debugging tool, only to discover that there's no information on how to interpret the debugger result sets! (SQLIOSim anyone?) That's why I love the Microsoft Advanced Windows Debugging and Troubleshooting blog. Another must-have on your Favorites list.

Enjoy!

-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. ;^)

The Perils of Hyperthreading for SQL Server

KKline — Sat, 18 Aug 2007 20:47:00 GMT

Just a quick note to send a big Thank You to Christoph Stotz of Frankfurt, Germany for his hospitality on Sunday. Thank you, Christoph!

Slava Oks has a rather well-known blog entry about how hyperthreading can negatively impact the performance of a SQL Server 2000 instance: http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx.

You might be wondering if those recommendations still hold true on SQL Server 2005?

Well, I have it on good authority (SQL Server MVP Geoff Hiten, FYI) that hyperthreading is still misbehaving on SQL Server 2005. Evidently, hyperthreading looks like a multi-core system to SQL Server 2005 thus triggering some "soft NUMA" behavior. As Geoff says,

"The real problem comes in the synchronization primitives that aren't hyperthreaded friendly (such as spinlock code). SQL 2000 had a bug that was fixed in build 910 that dealt with this issue. This bug was re-introduced in SQL 2005 when they changed the memory and scheduler to handle multi-core processors and NUMA architectures. So, in short, I would turn off HT on SQL 2005 host computers as the default. As always, your mileage may vary."

Great advice, Geoff!

Thanks,

-Kevin