Search results matching tags 'Miscellaneous' and 'Database Administration'

There is no such thing as a “Small Change” to a production database

jmkehayias — Wed, 04 Aug 2010 01:50:48 GMT

It seems like every week I get hit up with some kind of “Its a view only for this specific report,” or “It is a simple change that the vendor would have made if they had access” type of request related to SQL Server. If you’ve never dealt with me, let me start off this blog post by saying that I am a staunch advocate of Change Control Processes for production systems, I don’t care how simple you might think the change might actually be. Personally, I submit change requests for even the most simplistic tasks like creating a new index in the database or even changing the frequency of DBCC CHECKDB commands on production servers. Why do I do things like this? Basically put; because you never know!

Early on in my career with SQL Server, I learned the impact that so called “Small Changes” to a SQL Server database could have in a production environment. As a Business Analyst I had elevated access to a production system, which I really had no business having, that I did a significant amount of work on. One of my primary job functions was to prevent bills from going out to customers that contained errors on them, so I created a table in the database to exempt accounts from the billing process by inserting the account primary key into the table, which was then checked during Billing Nomination to exclude those accounts until the associated problem was fixed. So that I had a full history of when an account was added and removed from the table and by who, a audit table was added with auditing triggers for INSERT/UPDATE/DELETE on the exception table.

At some point, someone decided to alter this table and add an additional column to it to make it so they didn’t have to join to the accounts table to get the LDC (local distribution company) account number for troubleshooting problems with the accounts. When this was done, it broke reports that I had written for our CTO at the time because now the column name for the LDC account number existed in two of the tables, so ~~being young, dumb,~~ thinking that this column wasn’t needed, I mean really how hard is it to write a JOIN, I dropped it from the table. This is where the walls came crashing down.

Being relatively new to SQL there were a lot of things I didn’t know, like for example, SELECT * is dangerous to use, especially in auditing triggers when the table definitions don’t match. When i dropped the column from the base table, I didn’t change the audit table to match, causing the columns to mismatch and the trigger to fail. Now if the only thing that actually used this table was me, stupid assumption on my part, this wouldn’t have been a big deal, but the new account creation process had been recently changed to add new accounts to this table to create a billing hold during the account setup period. This is actually what broke, and it broke very fast causing a cascading effect of problems across multiple systems. It took the DBA and application developers a bit of time to trace this down and when they did I got a phone call asking what I had changed.

Such a small change, such a big mess. The impacts of this solidified for me that there is no such thing as a small change to a production database very early in my career. It took multiple people nearly two hours to cleanup the residual effects of this small change, primarily because a new account manager had to manually reenter the failed new accounts into the system and let them process again. I’d like to say that I never made a change outside of change control processes after this again, but I’d be lying if I did. However, I can say that I haven’t made untested adhoc changes like that ever again, and it has been over two years since I have made any undocumented change to a system. (Well at least intentionally, I have made mistakes and been connected to the wrong server and done seriously bad stuff like dropping a 80GB table which is why I use SSMSToolPack to color code my servers now.)

Should SQL Server advanced features be available a la carte?

jmkehayias — Tue, 22 Jun 2010 02:32:00 GMT

SQL Server users love the new features inside of the platform and each new release brings more exciting enhancements that motivate end users towards upgrading from previous editions. However, one of the biggest gripes I see from the SQL Server Community is that the hot new features are primarily Enterprise Edition only. Today I read a blog post by John Magnabosco over on Simple Talk titled “What If TDE Was Available In Standard Edition?” that once again brought up the subject of a newer Enterprise Edition feature that “should” be in Standard Edition according to John. Now to be perfectly fair to John, who I don’t know but I am sure he is a great person, the argument he makes in his blog post has been made before. The key statement in his blog post is:

You shouldn't have to be a large company with a large budget to create a secure environment.

To a degree I couldn’t agree with him more, but you don’t have to have TDE to create a secure environment. TDE is the icing on the cake in my opinion. However, in the not so recent past, this same type of argument has been made about other aspects of Enterprise Edition by the community. At PASS Summit 2008, which happened to be my first Summit and my first year as a MVP, during a insider session with Microsoft, some of the better known MVP’s drove the point home to Microsoft that stability shouldn’t be an Enterprise only feature. What specifically were they talking about? The ability to Lock Pages in Memory with 64 bit SQL Servers that can now scale memory beyond the previous limits of 32 bit architectures and with potentially devastating consequences if the SQL Server working set got trimmed or paged out by Windows Server 2003 under memory pressure. The result was a Cumulative Update to SQL Server allowing a this in Standard Edition.

Another big feature that debutted in SQL Server 2008 RTM was backup compression, which offered huge performance improvements and storage savings for backing up SQL Server databases. A number of third party tools have existed for a long time offering this feature including LiteSpeed by Quest Software and SQL Backup by Redgate, both of which are still industry standard tools for SQL Server backups despite the feature being available in the RTM of SQL Server 2008. Why are they still industry standard? Simple, backup compression was introduced as a Enterprise Only feature, and for $300-800 a third party tool makes much better financial sense versus the cost of an Enterprise license over a Standard license. Not to short change the third party tools, they offer a number of other awesome features beyond backup compression as well. As of SQL Server 2008 R2 RTM, backup compression is now available in Standard Edition of SQL Server as well.

So it begs the question, of this blog post. Should SQL Server advanced features be available a la carte? The competing RDBMS vendors have been selling features a la carte for a long time now. Are we to the point that SQL Server will also join the ranks with Oracle, Informix, and Sybase (this is an old link I know) on this? Would that even make the SQL Server community happy, or would we just begin complaining about the added costs on top of the base cost of the platform?

At some point Microsoft has to monetize their efforts on the product and the advanced features of it, there is after all a significant cost associated with developing, testing, releasing, and supporting it. SQL Server 2005 brought a number of features into the Standard SKU that were previously Enterprise only features, while also introducing a number of newer features in the Standard SKU, as well as a number of Enterprise only features. Coupled with this, the SQL Express platform is extremely robust for FREE and in SQL Server 2008 R2, now supports 10GB databases, which is larger than a number of the most basic databases in use by a lot of systems I encounter in consulting work.

What are your thoughts? What Enterprise only features do you think should be in Standard Edition? Should SQL Server features be available a la carte allowing you to determine what’s the most important to you, and if you are willing to pay for just those features?

Does the tempdb Log file get Zero Initialized at Startup?

jmkehayias — Fri, 14 May 2010 00:13:53 GMT

While working on a problem today I happened to think about what the impact to startup might be for a really large tempdb transaction log file. Its fairly common knowledge that data files in SQL Server 2005+ on Windows Server 2003+ can be instant initialized, but the transaction log files can not. If this is news to you see the following blog posts:

Kimberly L. Tripp | Instant Initialization - What, Why and How?
In Recovery... | Misconceptions around instant file initialization
In Recovery… | Search Engine Q&A #24: Why can't the transaction log use instant initialization?
Tibor Karaszi : Do you have Instant File Initialization?

The thought occurred to me today that despite having log files 8GB in size for tempdb, I’ve never really noticed that it takes that long for SQL Server to startup. So I jumped on twitter and shot a tweet out to Paul Randal (Blog | Twitter) and I also included the #sqlhelp hash tag to see what others in the community thought. I got a couple of comments, one linking me to Paul Randal’s blog post, another saying test it, and another saying the transaction log is always zero initialized.

When I got home tonight I was still thinking about this and as I went to go test it, I remembered that I had read a blog post once about a trace flag that would output information about zero file initialization. A quick search and I found the post on the Premier Field Engineers Blog. So I jumped on a test system I have and added the –T3004 and –T3605 trace flags to the startup parameters. Since the PFE blog provided the following disclaimer about using these trace flags so will I.

WARNING: These trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.

After setting the flags I checked tempdb and it was currently setup with a 4GB transaction log file. With this information in hand I restarted the instance and once it was online I opened the ErrorLog to look at what –T3004 could tell me about tempdb log file initialization.

2010-05-13 18:42:13.52 spid12s     Clearing tempdb database.
<……….skipped content…………>
2010-05-13 18:42:30.93 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 2 to 17 (0x4000 to 0x22000)
2010-05-13 18:42:30.93 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf
2010-05-13 18:42:30.93 spid12s     Starting up database 'tempdb'.
2010-05-13 18:42:30.96 spid12s     FixupLogTail(progress) zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from 0x5000 to 0x6000.
2010-05-13 18:42:30.96 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2010-05-13 18:42:30.96 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

Hmm, so the log file is zeroed for tempdb, but it doesn’t take a rocket scientist to notice that the page counts being zeroed out (17-2=15 total pages) don’t add up to 4GB of space. To check this, I created a new user database with a 8GB data file, and a 4GB log file to see the output for zeroing out a 4GB transaction log.

2010-05-13 18:45:42.61 spid54      Zeroing L:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\ZeroLog_log.ldf from page 0 to 524288 (0x0 to 0x100000000)
2010-05-13 18:46:20.92 spid54      Zeroing completed on L:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\ZeroLog_log.ldf
2010-05-13 18:46:36.35 spid54      Starting up database 'ZeroLog'.
2010-05-13 18:46:36.36 spid54      FixupLogTail(progress) zeroing L:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\ZeroLog_log.ldf from 0x5000 to 0x6000.
2010-05-13 18:46:36.36 spid54      Zeroing L:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\ZeroLog_log.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2010-05-13 18:46:36.37 spid54      Zeroing completed on L:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\ZeroLog_log.ldf

So a user database with a 4GB log file will zero out 524288 pages which is 4GB of space. I got a private message on twitter about the topic from Remus Rusanu (Blog | Twitter), telling me that the entire file isn’t initialized at startup, but if you use ALTER DATABASE to grow the size of the tempdb log, the space you grow by will be zero initialized entirely, so to test that I grew the log file out to 8GB in size:

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 8388608KB )

When I ran this, it returned completed immediately, so I jumped over to the ErrorLog to pull the results and was momentarily confused by what I got back.

2010-05-13 19:20:24.02 spid57 Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 524288 to 524319 (0x100000000 to 0x10003e000)
2010-05-13 19:20:24.02 spid57 Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

It only initialized 31 pages?? Can that be correct? So I went back and checked my log files actual size and I remembered this little problem mentioned by Kimberly Tripp on her blog post Kimberly L. Tripp | Transaction Log VLFs - too many or too few? that occurs when you grow the file in 4GB increments. So I reran the ALTER DATABASE statement and this time it took a minute for the command to complete. Much better, now I know we did some zeroing out of the file, and the ErrorLog output proved it:

2010-05-13 19:21:17.47 spid57 Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 524319 to 1048576 (0x10003e000 to 0x200000000)
2010-05-13 19:21:49.54 spid57 Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

Much better, so now the begging question is, why isn’t the log file zero initialized in its entirety at startup? I can’t speak with complete certainty on this, but I would guess that it has to do with the fact that the tempdb transaction log is never used for crash recovery, so it doesn’t really matter that the space isn’t zero initialized. Paul Randal explains in his blog post, In Recovery… | Search Engine Q&A #24: Why can't the transaction log use instant initialization?, how the parity bits are used during crash recovery to identify where recovery should stop processing log records. Perhaps full zero initialization is skipped for the tempdb log at startup because the log is never used for crash recovery, but that doesn’t explain why the log, when grown, does perform full zero initialization, unless it is due to the way that the log can wrap around, for example based on the last image above (VLF Usage After Log Reuse), if the log space continues to be used without truncation when the log gets back to FSeqNo 29, the log will have to grow causing the allocation to become non-sequential since FSeqNo 30-35 are still active. Maybe someone else will explain the reason why the tempdb log has to be zero initialized when grown but not at startup better.

EDIT:

After posting this, I jumped back over to twitter and saw some interesting comments from Brent Ozar (Blog | Twitter) that made me go back and test the impact of deleting the tempdb files from disk and then starting the instance up. When I did this, the entire file was zero initialized

2010-05-13 20:21:06.21 spid12s     Clearing tempdb database.
2010-05-13 20:21:06.23 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 0 to 1048576 (0x0 to 0x200000000)
<……….skipped content…………>
2010-05-13 20:22:22.81 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf
2010-05-13 20:22:22.84 spid12s     Starting up database 'tempdb'.
2010-05-13 20:22:22.85 spid12s     FixupLogTail(progress) zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from 0x5000 to 0x6000.
2010-05-13 20:22:22.85 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2010-05-13 20:22:22.86 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

So the answer is YES the tempdb transaction log is completely initialized when it is first physically created, but after that, its not zero initialized entirely as the instance starts up. There is a definite difference in startup times on my server when I deleted the files.

With Files

2010-05-13 18:42:12.54 Server Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64)
2010-05-13 18:42:31.04 spid8s Recovery is complete. This is an informational message only. No user action is required.

Without Files

2010-05-13 20:21:05.49 Server Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64)
2010-05-13 20:22:22.97 spid9s Recovery is complete. This is an informational message only. No user action is required.

When the files preexist, instance startup only took 19 seconds, without the files it took 1 minute and 17 seconds. This actually makes the zero initialization during log growth make sense. I’d say a fair bit of my misunderstanding of this is the way tempdb is often referred to be as being recreated at restart. Its not actually recreated based on these tests, but it is cleared, as show by the “Clearing tempdb database.” log entries. Interesting stuff.

ANOTHER EDIT:

So after thinking about this some more, curiosity got the best of me and I wanted to know what would happen if the size of the tempdb log file on disk was different from the size configured for tempdb. To test this, I used ALTER DATABASE to change the size of the log file from 8GB to 1GB and restarted SQL Server. This reduced the size of the log file on startup from 8GB to 1GB and the log showed that it didn’t zero initialize the entire file:

2010-05-13 21:20:45.92 spid12s     Clearing tempdb database.
<……….skipped content…………>
2010-05-13 21:21:02.59 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 2 to 17 (0x4000 to 0x22000)
2010-05-13 21:21:02.59 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf
2010-05-13 21:21:02.62 spid12s     Starting up database 'tempdb'.
2010-05-13 21:21:02.63 spid12s     FixupLogTail(progress) zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from 0x5000 to 0x6000.
2010-05-13 21:21:02.63 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2010-05-13 21:21:02.64 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

In addition to this, the startup time was fast, taking only 17 seconds to complete recovery of the instance.

2010-05-13 21:20:45.23 Server Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64)
2010-05-13 21:21:02.78 spid9s Recovery is complete. This is an informational message only. No user action is required.

So I shut down SQL Server and renamed the log file to templog_small.ldf and then restarted SQL. Once recovery completed, I grew the transaction log back to 8GB and once again shutdown SQL Server. Then I renamed the current 8GB log file to templog_big.ldf and renamed templog_small.ldf to templog.ldf, replacing the 8GB log file with a 1GB log file. SQL Server recognized the change during startup, and once again zero initialized the entire log.

2010-05-13 21:25:11.72 spid12s     Clearing tempdb database.
2010-05-13 21:25:11.73 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 0 to 1048576 (0x0 to 0x200000000)
<……….skipped content…………>
2010-05-13 21:26:17.95 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf
2010-05-13 21:26:28.05 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 2 to 17 (0x4000 to 0x22000)
2010-05-13 21:26:28.05 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf
2010-05-13 21:26:28.07 spid12s     Starting up database 'tempdb'.
2010-05-13 21:26:28.09 spid12s     FixupLogTail(progress) zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from 0x5000 to 0x6000.
2010-05-13 21:26:28.09 spid12s     Zeroing T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf from page 3 to 483 (0x6000 to 0x3c6000)
2010-05-13 21:26:28.10 spid12s     Zeroing completed on T:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf

This once again impacted the time required to recover the instance.

2010-05-13 21:25:11.01 Server Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64)
2010-05-13 21:26:28.23 spid9s Recovery is complete. This is an informational message only. No user action is required.

So that leaves me asking, does SQL Server really recreate tempdb from the model database every time it starts? According to KB Article 307487 :

“When SQL Server starts, the tempdb is re-created by using a copy of the model database and is reset to its last configured size.”

It would certainly seem as if this statement is wrong based on testing. What I find the most interesting is if I reverse the process and replace a 1GB log file with a previously created 8GB log file, SQL Server doesn’t zero initialize the 8GB file, it just shrinks it back to 1GB. It only performs the zero initialization as a part of having to grow the preexisting log file during instance startup.

Why the “Toilet” Analogy for SQL might be bad

jmkehayias — Sat, 08 May 2010 06:33:41 GMT

Robert Davis(blog/twitter) recently blogged The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads, in which he uses an analogy for why increasing the value for the ‘max worker threads’ sp_configure option can be bad inside of SQL Server. While I can’t make an argument against Robert’s assertion that increasing worker threads may not improve performance, I can make an argument against his suggestion that, simply increasing the number of logical processors, for example from 4 to 8, can resolve the problem.

Why do I make an argument against this? It’s really quite simple, you can easily double the number of logical processors inside of SQL Server, by enabling hyper threading at the BIOS level for the server. While this feature of Intel processors doubles the number of logical processors available to the system, thus doubling the number of schedulers available to SQL Server, it isn’t without cost. Now, to be perfectly fair, Robert doesn’t mention in his blog post any concept of increasing the number of logical processors by enabling hyper threading for the server, but its not hard for someone that is unfamiliar with SQL Server scheduling to come to that conclusion.

It is unfortunate that Robert uses a easy to misinterpret/misconstrue example of simply doubling the logical processors, which in turn doubles the number of schedulers inside SQL Server. Whether or not enabling hyper threading will help or hinder SQL Server depends on a number of factors, not the least of which is the type of processor in use on the server. Why does the type of processor make a difference? I am very happy that you asked. It makes a difference because the size of the on-chip cache, as well as the number of processor cores affects how efficiently the processor can make use of hyper threading under memory dependent workloads, like that generated by SQL Server. Older processors with smaller caches, can be prone to cache miss problems that can negatively impact performance, while newer processors may not have this same type of problem under the same workload with hyper threading enabled.

One of the key points that Robert accentuates in his blog post, is that appropriate testing should be performed for changes to the system to ensure that the change has the anticipated effect. On some systems, enabling hyper threading, and doubling the number schedulers available inside of SQL Server, might just be the needed change to accommodate a given workload, without having to scale up the physical hardware for the server. However, the same system may be prone to bottlenecks under a different workload. On older hardware, hyper threading has been recommended against, but my own testing on newer hardware (Xeon 5530) processors, have shown the the impact of enabling hyper threading can be beneficial, or at worst negligible depending on the type of workload being generated.

The biggest takeaway from Robert’s blog post is to properly test any type of configuration change before actually implementing it in production.

Have you got air in your spare tire? (Have you checked your DR/HA plans?)

jmkehayias — Wed, 21 Oct 2009 14:13:46 GMT

This morning I was late to work because as I backed out of the driveway I noticed that my wife's car had a flat tire. This should be a pretty simple thing to fix, but it turns out that the spare tire was also flat. This too should have been an easy fix because I have a 60 gallon air compressor standing in the corner of my garage that is always charged with air, but as luck would have it since I don't use it to fill tires with air that often (it was used primarily for media blasting my Mustang last year), I couldn't find the correct air chuck I needed to air the tire up. This resulted in a trip to the local gas station to air the spare tire up and then back to the house to put it on the car.

This story serves as a good reminder to test your DR/HA plans before you actually need to use them. Most people keep a spare tire in their trunk to cover the occasion that you have a flat on the road, to get you to the nearest tire shop (at least I do). However, if I had actually been on the road, my spare was quite useless since it was completely flat. I keep another option in my pocket with AAA, so if I had been on the road, I could have called them to come out. However, what if it was a true emergency situation where I had to get to the hospital for something? Do I really want to wait a half an hour or more for support to show up to fix what should have been a simple problem with proper tests/checks ahead of time.

My wife knows how to change a tire, she's done it before, but she's not as good at doing it as I am. She's kind of like our Jr. DBA, she can do the work, but if I am around it is sometimes faster/easier for me to just take care of things. If I hadn't been home, she wouldn't have been able to fix the problem anyway, she would have had to call AAA for assistance. This leads to my second note, you should plan for and know your support options before you are in a problem situation. One of the documented steps in our run book for when I am unavailable is to call Microsoft Support if a problem seems to complex, or is beyond the available knowledge for troubleshooting. Certainly this is going to take longer to reach a solution to the problem, but it can be faster than if you try to hack your way to a solution manually (walking to the gas station to put air in the tire would have taken longer than waiting on AAA I am sure).

So my question to you is, have you checked your DR/HA strategy lately, and when's the last time you checked your spare tire pressure? If you have, do you know where all of the tools are in case you actually need them? Thankfully this morning the problem occurred in a controlled environment that allowed me to solve it without to much delay. I checked the spare in our van, and it too was flat, so I aired it up as well, future crisis averted hopefully.

Another use for the Default Trace: Mapping Temporary Tables to Sessions

jmkehayias — Tue, 29 Sep 2009 09:06:00 GMT

Here’s another use for the information captured by the default trace. Ever wonder what session created the tables that exist in tempdb? Well you can find it using the default trace and the sys.objects view in tempdb. Any time an object is created in a database, EventClass 46 (Object:Created) is captured in the trace output of the DMV. By scanning the trace files for a matching ObjectID that was created 100 ms before or after the objects create_date with DatabaseID = 2 (tempdb) you can match the objects back to the SPID (session_id) that created the object:

DECLARE @FileName VARCHAR(MAX) SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1; SELECT o.name, o.OBJECT_ID, o.create_date, gt.NTUserName, gt.HostName, gt.SPID, gt.DatabaseName, gt.TEXTData FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt JOIN tempdb.sys.objects AS o ON gt.ObjectID = o.OBJECT_ID WHERE gt.DatabaseID = 2 AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events) AND o.create_date >= DATEADD(ms, -100, gt.StartTime) AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

If you’ve seen my presentation for the PASS DBA SIG (no longer a SIG but a Virtual Chapter), you will notice a difference in how the @Filename variable is being built in the above code. Previously I used the fn_trace_getinfo function to get the filename and did a long string parse to go back four file numbers to the current start file. This is not necessary however if you just use Log.trc as the filename since it will automatically roll forward through the existing files from this point which makes for cleaner code in my opinion.

If I have missed something and there is a better way to do this using DMV’s please post a comment and let me know.

SQLCAT Technical Note: Resolving PageLatch Contention on Highly Concurrent Insert Workloads Part 1

jmkehayias — Wed, 23 Sep 2009 01:29:32 GMT

Its funny to see this new technical note posted by the SQLCAT team today because we just recently had a discussion about this on Twitter. My good friend Sankar Reddy (Blog/Twitter) sent me an IM with the link to this page based on the twitter discussions.

Essentially, the problem deals with inserts into a table with a identity column as the primary key and clustered index key. Under a busy enough workload all inserts occur on the same page at the end of the B-Tree and the processes wait on the Page Latch to be released on the page. In this technical note, the SQLCAT team covers how to deal with this scenario using Partitioning and hashing.

http://sqlcat.com/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx

Update - SQL Blocked Process Monitor

jmkehayias — Wed, 29 Apr 2009 01:18:35 GMT

Due to dependency requirements I had to split this tool into two separate tools one for SQL Server 2005, and one for SQL Server 2008 today, and I have reposted the newer builds on the codeplex site:

http://sqlblockedprocessmon.codeplex.com/

one build is specific for SQL Server 2005, and can be used if all you have installed on the machine that you are running the tool on is SQL Server 2005. If you have SQL Server 2008 installed on the machine that you are running the tool on, or if you have the SQL Server 2008 Management tools installed on the machine, you can use the SQL Server 2008 version of the tool. Keep in mind that the correct tool depends on what is installed on the computer that is running the tool, not the server that the tool is connecting to (there is a -S parameter that allows you to specify to connect to a different server).

Background of this change:

Within a few hours of being at the office today, I was contacted by TheJackal101 who had attempted to use the tool I published late last night on Codeplex. As a DBA by trade, and a Developer hack by night, I apparently still have a bit to learn about testing my code before actually releasing it into the wild like I did last night. For some background, on my Laptop, which is where I do most of my development work and initial testing, I have SQL Server 2005 Developer and Express, and SQL Server 2008 Developer and Express Editions, Visual Studio 2005 and 2008, and a bunch of other odds and ends installed.

When I wrote out the initial version of this tool, I was targeting a SQL Server 2005 Ent. Edition server and chose to use the SQL Server 2008 SMO classes since they are the most up to date, and I had them available to me. It was able to connect to my SQL Server 2005 server instance with no problems, and after some quick tests, I put it to use running against the production server that was having blocking problems, and let it collect data. The information gathered proved to be pretty useful, although it was a bit more copious than I had initially expected, and the app was not very configurable at this point as I had basically hard coded everything in really quick so that I could solve my problem.

After some rework, I had an app that I thought was ready for a community alpha test, however, there was a serious shortcoming in the coding of the application, it required that you have .NET 3.5, and SQL Server 2008 SMO installed for it to function. This was immediately clear when I got the tweets from TheJackal101 and I confirmed this by running the tool directly from one of my SQL Server 2005 Servers and I immediately got errors for missing reference assemblies. The solution is the split projects and dual builds at this point, but I have some bigger plans in store for the future of this tool, be on the lookout for future updates.

Using SQL Client Configuration Alias to Create Linked Server

jmkehayias — Tue, 14 Oct 2008 23:07:14 GMT

If you have a named instance of SQL Server and you need to create a Linked Server to it, using the SQL Native Client will result in a Linked Server like SQLDEMO\SQLEXPRESS. This isn't ideal for coding against. One thing that you can do is create a Client Configuration Alias to provide a common name for the instance that you want to connect to, for example EXPRESS. To do this, first logon to the SQL Server through remote desktop and open the Client Configuration Tool by clicking Start and then Run:

Then click the Alias tab:

Then Click Add, and then click the TCP/IP button. Type in the Alias Name, and then type in the Instance path:

Click OK twice, and then open SSMS and connect to the SQL Server instance that will have the linked server created. Create the new linked server, and Specify SQL Server and use the Alias name instead of the actual instance name:

If all went well, then the Linked server will connect and be available for querying:

Other uses for this could be to create a loopback linked server in SQL Server 2005. You can use TCP/IP and specify to use the loopback IP 127.0.0.1 for the ServerName. This will allow you to create a linked server to itself.

What other scenarios can you think of that Client Configuration Aliases will make life easier?