Odds and Ends - Zach Nichter

How do Multiple Instances Affect your Server

There are some pretty obvious challanges to managing multiple instances of SQL on the same box. Most of which are performance and troublehooting issues. Like with most heavily loaded SQL servers (single instances) there are the problems of job management, however, with multiple instances it's a little more complex. There are many other problems that are inherent with multiple instances but I won't list them all here.

In my mind one of the biggest issues is how is it affecting system resources, and, are resources waisted or causing contention by having multiple instances? In my current gig we have a 2 node active\active cluster running 7 (yes I said 7) instances of SQL (I'm fighting the battle of a perception that this is ok with management). These machines are x86 machines with 4 HT procs and 16GB RAM each and they are attached to a SAN. The instances were laid out logically to divide applications to different instances, buisness apps, olap, default instance requirements, financial apps and so on. This was all done during a consolidation project far before my time here and with no specific internal SQL knowledge at the time.

I have been working on educating the staff with SQL internals and the affects that this architecture has on the system, what counters to look at etc. This MS whitepaper explains, fairly well, the performance affects of having multiple instances on a mchine and fit's my arguments of single instances and x64.

Anyway, enjoy and have a good weekend,
Zach

TempDB: Using Multiple Files in SQL 2005

I have seen in a couple of places where it is recommended to continue to use multiple files for TempDB in SQL Server 2005. Recently we had a MS consultant on site that was able to contact the SQL Product Development group about that question in particular. Here is the responce that I got in return.

______________________________________

The information in KB 328551 is exactly why I don’t think any recommendation based on number of processors is of much use.

This recommendation has been cut and pasted for several versions of SQL Server now, and while it may have worked great for SQL 7 and SQL 2000 when most machines had just 1-2 processors or a really large server may have had 4 processors. Now with SQL Server 2005, many more servers are starting with 2 sockets with dual core processors (4 cores) and then enabling Hyper threading to give the appearance of 8 processors to the OS.

Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate. However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.

On the other hand, the application might make extensive use of tempdb, but the bottleneck is not on the schema lock, but rather on the I/O throughput. Unless the additional files are created on different disks, then just adding more tempdb files won’t help this either.

Adding tempdb files only helps if the issue is around schema lock contention. This is another point worth bearing in mind, and one that’s not addressed at all in the number of files = number of processors recommendation.

All these points help illustrate why the recommendation for tempdb on a completely new system should be

1.       Start with a single tempdb file
2.       Monitor for tempdb related performance issues
3.       If the bottleneck is the schema lock then consider adding more tempdb files until the contention is resolved.
4.       If the bottleneck is I/O throughput then consider adding disks to the array backing the tempdb files, or create additional tempdb files on additional disks.

Since you already have an existing system, you should monitor the tempdb usage and bottlenecks (you have the perfmon logs already), and use this information to help determine the starting number of tempdb files, and the number of disks backing the tempdb files.

As always this is just a starting point as new server hardware is invariably faster, has more memory, and faster disks, which will most likely change where the next bottleneck is.

________________________________________

Anyway, thought I would pass it along.

-Zach

Pictures of my boys

Now I know most of you dont care, but I have posted pictures of my boys in the gallery. I was showing them what daddy was working on and they wanted me to put pictures of them on the stuff that I work on (that's about as technical as it gets with my family).

-Zach

Tough Row to Hoe (Part 2)

Alright, so there is a spreadsheet that I am attempting to deliver on to you guys that read Tough Row to Hoe. I am working on cleaning up my DMV spreadsheet a bit, and figuring out a way that I can post the Excel file that I created it in.

Now with the RTM version of 2005 almost all of the DMV's are documented but when I started the task of organizing all of the DMV's and documenting them there was very little information in the BOL about them. So now you can find what you need in BOL in reference to the DMV's so I thought I wouldn't need my spreadsheet anymore.

Well, this has turned out to not be true. I have had multiple situations where I was doing something in 2005 and I had no idea what DMV I would need to look at to find the information I was researching. With the way I organized my spreadsheet I have found that it's extremely easy to find what I need in seconds. I am working on adding the links to the BOL entry for the views so that I can look at the info there too, but that is in the next version.

In the mean time does anyone know how to attach a file to a blog enrty here?

Thanks,
Zach

Science: Kinetic Energy, CPU physics

So I know this is not SQL Server specific information but these are a couple of the things I have read recently that I have found interesting.

There was an incredible discovery at the Sandia National Lab that could mean a great deal, potentially, for our world. Sandia recently reported being able to heat steel and tungsten to 2 billion degrees Kelvin for 10 billionths of a second (Wow, I wonder how they accomplish measuring something in billionths of a second, much less measuring something that is 2 billion degrees). The process that the metal goes through was interesting too.

They took a spool of steel threads, the spool was about the size of a coffee cup and the threads of steel were thinner than a human hair. Then they pumped 20 million amps of current through the core (spool), and the current vaporizes the core into a cloud of ions (plasma). There is a magnetic field from the current that takes the ions and squishes (that's right I said it, squishes) the plasma to a thickness about the size of a pencil lead with a velocity that could travel a plane from New York to San Fran in just a few seconds. Then for a short period of time (no doubt measured in 10s of billionths of a second), the plasma ions, once squished (yup, there's that word again and I'm sure the appropriate word is squashed but I like squished better), has no place to travel because it has been condensed to it's most compact form, therefore keeping the ions from traveling and should have kept the ions from producing energy. But the ions continued to produce energy and during this period is when the temperatures became super charged and produced the 2 billion degree heat. I read from one source (and there are many out there) that they may have discovered a new state of matter.

One thing that I think was pretty significant was " the radiated x-ray output was as much as four times the expected kinetic energy input", that was interesting in itself, check it out if your interested. Anyway, I thought that one at the very least, blog worthy.

The second interesting find was on the future of CPU's. This was an article I found on ZDNet, and it refers to Moore's Law as coming to end of life with current processor technology. The article stated that "Simply put, today's devices, which are based on complementary metal oxide semiconductor standards, can't get much smaller and still function properly and effectively. That's where spintronics comes in," said UCLA engineering professor Kang Wang, who will act as director of the institute."

Spintronics is a technology that is being invested in by the big chip manufacturers. The spintronics technology has something to do with (I'm no engineer) the spin of electrons as they are charged and passed from one point to another (I can picture engineering geeks everywhere just elated with the possibilities). Anyway, short but interesting read.

Enjoy,
Zach

Transactional Database Structures, Intriguing...

I subscribe to Jason Haley's blog and there was an interesting blog reference today on "Transactional" Database Structures. I have to admit I have never worked on a database that has required this kind of design before and I found myself intrigued by the concept, I like the simplicity and complexity of it.

He is referencing an architecture that uses purely inserts and no updates along with a checksum to show whether or not a client is active or inactive based on whether they have made their payments to the provider. He also summarizes the pro's and con's of using this architecture.

Because I have no working experience with this structure and architecture I don't know what the performance caveats would be so I can't say whether or not I support it, but I will say that I do find it interesting.

It's a good read to get the brain going though.

-Zach

Interview with Louis Davidson

Check out the podcast interview of Louis Davidson on SQL Down Under's web site. It really is a great concept. There are 11 other mp3 downloadable interviews with people that have great SQL knowledge (Kalen Delany, Adam Machanic, Tom Moreau, Itzik Ben-Gan) and others.

Enjoy,
Zach

SQL Server 2005 System Map

If you haven't heard it yet (and it has more than likely been blogged about a 100 times over) the SQL Server 2005 system map is downloadable off of Microsoft's Download site. Check it out for yourself. I can't wait for the compiled help version of this to come out.

Enjoy!
Zach

Movin On

Recently I made a decision to go out on my own. I am taking a contract for a few months while I build up and start my own business.

I know that I havent blogged at all in the past few months and I'll be starting up again shortly.

-Zach

Tough Row to Hoe

Man, I know that you all have been patient, and it seems like forever ago that I told you all that I would be posting my findings for the DMV's. I have probably spent over 80 hours researching DMV's and I still feel so far from ready to post anything. Problem is, I am reasearching rabbit trails as I find them, and while this is good for discovery it keeps me from my original goal. Work has been catching up with me too.

I promise to finish at least a couple of these before the week is out so I can post something.

Sorry to keep you waiting,
Zach

SQL Server 2005 Migration

A friend of mine, Darshan Singh, wrote this article for SQL Server magazine. It's a great article for those of you that are looking to make the migration from 2000 to 2005. The article covers two methods of migrating SQL Servers (in-place upgrade) and databases (side-by-side upgrade).

Enjoy the article, it's very well written, clear, and has some great content.

Enjoy,
Zach

DMV Research

I have been doing a great deal of research on the DMV's, functions, stored procs and tables that are shipped in 2005. During my adventure I have learned a lot along the way. I have collected a ton of great information and have had some luck finding information in a lot of great places. Some of the information that I have picked up has been a result of just trying to understand exactly what it is I'm looking at when I query a DMV. This stuff can be mind boggling when your not a developer and there is not a bit of information available about it on the web.

I have been collecting the information and breaking the DMV's, functions and stored procs up into categories and trying to derive what the object is doing, then determining how it could be used and maybe giving some query examples.

Look for some of the results soon.

-Zach

DMV's - Query Troublshooting

I read the SQL Server Engine Tips blog this morning about troubleshooting SQL statements in 2005. If you don't read this blog you really should consider checking it out. The blog uses the new T-SQL features and DMV's to retrieve information about queries that have run through the server.

There's a lot of great content in the post.

Enjoy,
Zach

What's Next?

This week I am doing research on 2 different SQL 2005 topics. The first is a collection of the DMV's I've heard about and grouping them together for usage. The second is testing DB Mirroring. I want to hammer mirroring and see how it performs. The plan is to write a couple of articles about the setup, performance and test results of the technology.

If there is anything in specific you would like to know about DB Mirroring, let me know and I'll try to fit it into the testing.

Later,
Zach

PASS 2005 - Day 2

Wow is all I can really say. I only got to show up today for PASS for a couple of hours but all I can say is WOW! This was my first PASS conference and man was it great. I got to see all of my old co-workers at Scalability Experts today and that was great, I really enjoyed seeing them. I saw all of the great booths and new products in the SQL Server market, which was a lot of fun. Met some new people and hopefully new friends. I really enjoyed the social side of the gathering. It was great spending some time with ex-coworkers of mine Randy Dyess, Rick Heiges, Erin Welker, Darshan Singh and Larry Chesnut. I met Louis Davidson and talked with him for a bit (very nice guy). And I got to stay for almost all of one session.

I went and saw Bob Dorr's session about the SQL Server 2005 OS Engine. Man was it great, the content was excellent and there was a lot of talk about how to use the system dmv's that PSS will use for trouble shooting issues, how to get to a sessions last error message, all of the statistics (OS, SQL, thread, etc.) for that thread when the error occured and so on. Man it was great and I wish that I could have stayed for the whole thing, but it was great while it lasted. Some of the DMV's he covered were...

dm_os_ring_buffer
dm_os_sys_info
dm_os_hosts
dm_os_stacks
Dm_os_wait_tasks
Dm_os_workers
Dm_os_schedulers
Dm_os_buffer_descriptors

SIDE NOTE: Have I ever told you how much I love OneNote? Dang, I love that app. It's a great app for taking notes at things like technical sessions.

There were awsome topics beinng covered and it was a lot of fun. Hope everyone that is there enjoys Friday.

Thanks PASS, you all did a fantastic job!!

-Zach