Single Source for the Latest SQL Server Jobs and Info -- SQL Server Reporter

273 posts from: PSS SQL Server Engineers

Slow query performance because inaccurate cardinality estimate when using anti-semi join following upgrade from SQL Server 2000

(Indexed 2010-09-01):

We have a few customers who reported that some of their queries run slower following upgrade from SQL Server 2000 to SQL Sever 2005, 2008 and 2008 R2. Specifically, queries experiencing the issue have anti-semi joins in the query plan and the join involves multiple columns as joining condition.Anti-semi joins are results of query constructs like NOT EXISTS, NOT IN. Here is an example of the query that would result in anti-semi join:SELECT t1.* FROM tst_TAB1 t1 WHERE NOT EXISTS( SELECT * ...(truncated)...

Query performance and table variables

(Indexed 2010-08-24):

Technorati Tags: Performance Frequently, we see our customers using table variables in their stored procedures and batches and experience performance problems. In general, these performance problems are introduced because of large number of rows being populated into the table variable. Table variables were introduced in SQL Server 2000 with intention to reduce recompiles. Over time, it gained popularity. Many users use to to populate large number of rows and then join...(truncated)...

Why use SQL Server 2008 R2 BPA? Case 1: Missing Updates..

(Indexed 2010-08-24):

In June I introduced you to a new Best Practices Analyzer for SQL Server, SQL Server 2008 R2 BPA: http://blogs.msdn.com/b/psssql/archive/2010/06/20/introducin...(truncated)... Ive seen some mixed reaction to this tool. But when I verbally talk to some about the type of knowledge CSS has put into the rules, they see a greater value. Therefore, I thought I would put together a series of blog posts with some examples of this knowledge. The first is something I call Missing Updates. While Microso...(truncated)...

How It Works: Timer Outputs in SQL Server 2008 R2 - Invariant TSC

(Indexed 2010-08-18):

[Image] I would love nothing more than to take you back to my high-school days running the 440 yard hurdles (yes yards not meters) where timing was done with a stop watch (you know the old, moving dial style) but timers on the PC don't allow that simplicity. I have discussed the timing behavior is SQL Server in previous blogs and it is time to discuss timing behaviors again as SQL Server 2008 R2 has been updated. How It Works: SQL Server Timings and Timer Output (GetTickCount, timeGetTim...(truncated)...

How It Works: Enumeration of sys.messages

(Indexed 2010-08-16):

I ran into an issue which has some aspects I found interesting and thought you might as well. When you do a select against the sys.messages virtual table the data is retrieved from the resource files (.RLL) files stored under BinnResource directory. This is done by loading the RLL library and retrieving the resource string information then materializing the row in the result set. What I found interesting is that by default SQL Server will materialize a row for each of the installed resource la...(truncated)...

Replay Result Set Event (Replay * Events)

(Indexed 2010-08-13):

From: Robert Dorr Sent: Friday, August 13, 2010 8:58 AMSubject: RE: SQL Server Trace Replay - "Replay Result Set Event" The Result Set event is one of several client side replay events. Here are some of Common Result Event ColumnsTextReturned values for example DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'- RETURNS - IsCloud as column name in result set event IsCloud = 0 as value i...(truncated)...

When a full dump isnt really a full dump

(Indexed 2010-08-04):

I was working on a customer issue which involved debugging a dump. The dump was generated via SQLDumper within Reporting Services. So, the name of the dump was similar to SQLDmpr0001.mdmp. When I opened the dump I saw the following: Loading Dump File [C:tempSQLDmpr0001.mdmp] User Mini Dump File with Full Memory: Only application data is available Which tells me we actually have a full dump. Well, that and the fact that the dump was almost 8GB. Through the co...(truncated)...

How It Works: Error 18056 - The client was unable to reuse a session with SPID ##, which had been reset for connection pooling

(Indexed 2010-08-03):

This message has come across my desk a couple of times in the last week and when that happens I like to produce blog content. The error is when you are trying to use a pooled connection and the reset of the connection state encounters an error. Additional details are often logged in the SQL Server error log but the 'failure ID' is the key to understanding where to go next. Event ID: 18056 Description: The client was unable to reuse a session with SPID 157, which had been rese...(truncated)...

SQL 2008 FileStream Fails to Enable After Setup on Cluster that uses Veritas Mountpoints

(Indexed 2010-07-29):

Recently a customer ran into an issue where they had successfully installed SQL Server 2008 SP1 on a 2 node Windows 2008 cluster. When they went to restore a database that was given to them, they found out the database was created with the new SQL 2008 FileStream feature and couldnt restore the database on their newly installed SQL 2008 SP1 clustered instance. So they went to enable FileStream through the SQL Configuration Manager. After clicking the check box Enable FILESTREAM for Transact-SQL ...(truncated)...

Installing SQL Integration Services after SQL Cluster Setup has Completed

(Indexed 2010-07-29):

Today I ran into an issue where, SQL Server 2008 SP1 was installed on a Windows 2008 cluster and was working just fine, but we wanted to install SQL Server Integration Services (SSIS) to the two nodes of the cluster. Since SSIS is not cluster aware, we thought it was be just a simple process of adding features to an existing instance of SQL Server. Unfortunately, it was not that intuitive. In setups SQL Server Installation Center you naturally select New SQL Server stand-alone installation or a...(truncated)...

Why does PREEMPTIVE_OS_GETPROCADDRESS Show a Large Accumulation?

(Indexed 2010-07-21):

There is a bug in SQL Server 2008 that causes PREEMPTIVE_OS_GETPROCADDRESS to include and accumulate the execution time of the extended stored procedure (XPROC). The following is an example showing the increase in the GetProcAddress wait time. select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP'exec master..xp_dirtree 'f:'select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP' GetProcAdd...(truncated)...

Sampling can produce less accurate statistics if the data is not evenly distributed

(Indexed 2010-07-09):

Recently I worked with a very knowledgable customer who called in and wanting to know things about statistics. This is because he noticed that his query would get inaccurate cardinality estimate due to inaccurate histogram. Specifically, he has questioned why AVG_RANGE_ROWS would be very high when he did 10% sampling. But it became very low (almost distinct) when he used 100% sampling. In order to illustrate the issue, let me create a fake table and popul...(truncated)...

Why is my SQL Clustered Instance changing authentication modes?

(Indexed 2010-07-09):

We get our fair share of cases related to SQL Server running ( or not running) on a Windows Cluster. I had one of them recently where the customer was seeing different authentication modes for SQL Server depending on which node of the 2-node cluster that it was online on. The Errorlogs document this behavior clearly as follows We see that Sql came online on node P1 it was in Mixed Mode 2010-05-08 05:17:41.13 Server Authentication mode is MIXED. 201...(truncated)...

Why doesn't SQL Server use statistics and index on this computed column?

(Indexed 2010-07-08):

In this post, I talked about how to use computed column to improve performance. By creating index on a computed columns, you can have two benefits. You get better cardinlaity esimate on the expression in your query and your query may also use that index to do seeks or scans.Lately, I have been helping a customer. They have a view which does an aggregate (group by) like this select ISNULL(c1,'0') + ISNULL (c2, '0') as compare_str, COUNT (*) from t group by ISNULL(c1,'0') + ISNULL (c2, '0')...(truncated)...

Revisiting an old SSRS performance topic again

(Indexed 2010-07-04):

Almost three years ago, I posted about why Reporting Services takes so long to respond to the first request if it has been sitting idle for a long time (like overnight). The original post can be found at http://blogs.msdn.com/b/sqlblog/archive/2007/11/09/reporting...(truncated)... For those of you who dont want to read it, the core conclusion was that (at least on my hardware), SSRS was taking about 30 seconds to start up after the application domain had been recycled. The post was i...(truncated)...

My Kerberos Checklist

(Indexed 2010-06-23):

Ive had numerous questions regarding Kerberos, both internally within Microsoft and with Customers. It continues to be a complicated topic and the documentation that is out there can be less than straight forward. Based on some previous items Ive worked on, I wanted to share my experience in regards Let me start by looking at two scenarios for reference. One that is basic and the other that is complex. [Image] [Image] As youll find, once we figure out how to configure the ...(truncated)...

Introducing the SQL Server 2008 R2 Best Practices Analyzer (BPA)

(Indexed 2010-06-21):

Some of you may have noticed I havent posted a blog in some time. Well, Ive been a bit busy working behind the scenes on a new tool we released this weekend, the SQL Server 2008 R2 Best Practices Analyzer (BPA). You may remember that I announced this new tool back in April at the PASS Europe Summit on this post. This past week final development and testing were completed on the tool and it is now available for you to download at the following location: http://www.microsoft.com/downloads/detail...(truncated)...

Known issues installing SQL 2008 R2 BPA relating to Remoting

(Indexed 2010-06-21):

After getting through the Pre-Reqs for BPA (PowerShell 2.0, MBCA, .NET Framework), you may hit one of two scenarios when installing BPA. In all of the cases of an install failure, you will see the following error: [Image] There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. In your Application Event Log, for both of these scenarios, you will also see the fol...(truncated)...

Where did the SQL Server Instance disappear? The clue may be in the WMI logs!

(Indexed 2010-06-12):

We recently worked with a customer who ran into an interesting situation. This problem deals with SQL Server 2005 Service Pack 3 setup. Normally, when you launch the SQL 2005 SP3 setup and you reach the screen which shows the components for which you can apply the service pack, you will get a list of all the product components. For a server with one default instance of SQL Server Database Services installed, the list will appear as shown below.[Image] In this customer’s scenario, there we...(truncated)...

How It Works: SqlDataReader::RecordsAffected and Why it Returns -1

(Indexed 2010-06-08):

I encountered another interesting research issue to share with you. The documentation states RecordsAffected are for INSERT, UPDATE and DELETE but it might fool you. ------------------------------------------------------------...(truncated)... Sent: Monday, June 07, 2010 9:06 PMSubject: "set nocount off" I am trying to figure out the logic behing set nocount. I want to get the row count when the procedure is executed so I can display a progress bar based on the returned rows (using SqlDataAd...(truncated)...

SQLIOSim - Is Error: Unable to get disk cache info really an error?

(Indexed 2010-06-08):

The short answer is that it is not an error and the message should be a WARNING. [Image] I looked at the latest, internal code base it has already been changed to a WARNING. if (!DeviceIoControl (volume, IOCTL_DISK_GET_CACHE_INFORMATION, NULL, 0, ...(truncated)...

How It Works: Spinlock Of Type LPE_BATCH

(Indexed 2010-06-08):

A question arose asking - "What does the LPE_BATCH spinlock type represent?" You can see the LPE_* series of spinlocks using the DMV query "select * from sys.dm_os_spinlock_stats" A spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of the spinlock is to be fast and efficient. So the ultimate goal is to see is 0 collisions and 0 spins. SQL Server 2008 contains 150+ spinlock objects to protect the various internal structures during multi-...(truncated)...

SQL Server 2008 R2 New Non-Yield Ring Buffer Information

(Indexed 2010-05-27):

In 2002 the SQLOS team added specific checks for non-yielding scheduler issues. You may be familiar with the 178** series of errors like 17883 scheduler non-yield. Since 2002 the test matrix for SQL Server has flagged these errors and corrected them. With the evolution of SQL Server 2005, 2008 and now 2008 R2 the number of self-inflicted 178** errors is less than I can count on one hand since 2007. We are finding that the vast majority of the 178** error conditions are caused by external ...(truncated)...

How It Works: The SQLAgent Log File

(Indexed 2010-05-27):

I am still working to resolve the customers problem but during my investigation I briefly looked a the SQLAgent logging format and thought you all might like some of these details. From: Robert Dorr Sent: Monday, May 24, 2010 9:47 AMSubject: RE: SPID in SLEEP state indefinitely The error itself is from SQLAgent while calculating the next scheduled execution time. IDS_POSSIBLE_DATE_CALC_SPIN "Warning [%ld]: Possible date calculation spin for Schedule %ld" We are trying...(truncated)...

Reporting Services, Scale Out and Clusters

(Indexed 2010-05-27):

Every once in a while, I get asked the question about deploying Reporting Services on a Cluster. Usually it is tied to a scale out deployment, sometimes it is not. I just was asked the question again by an engineer in our group. So, I figured I should put this out there to have some reference. For starters, in regards to Scale Out Deployment with Reporting Services, there are two references that I recommend you read. One is in Books Online and the other is a Scale Out Best Practices doc from th...(truncated)...

Dont touch that schema!!!

(Indexed 2010-05-18):

You know how every product that has an underlying database has documentation that says not to modify the schema? Do you always pay attention to that warning? If your product is Reporting Services, I just ran into a case today which I hope convinces you to keep your hands off!!! The problem was that the customer could not edit any of his subscriptions. They would run, but he could not modify any of their properties. Every time he would attempt to modify the subscription, he wo...(truncated)...

RS Content Types and SharePoint 2010

(Indexed 2010-05-11):

When creating a new document library within SharePoint you have a few options. You can just create a new Document Library, or you can go to More Options and choose Report Library. [Image] [Image] When you choose Report Library, by default, it will allow you to create a report, but still will not have all of the Reporting Services Content Types. If you go to the Library Settings, you will then have an option to add the Content Types. [Imag...(truncated)...

Follow-up to Questions from Europe PASS 2010.

(Indexed 2010-05-06):

I thought i would post some answers to questions I received during my pre-conference seminar at Europe PASS 2010: Q: You said that trace flag 2528 disables parallelism for DBCC CHECKDB. Is there any way to force CHECKDB to use a parallel threads? A: No. If the trace flag 2528 is not enabled and max degree of parallelism is 0 or > 1, then the engine can decide to use parallel threads to scan the information required for DBCC CHECKDB. How do you know? Well, one way is to see if you see CXPACKET ...(truncated)...

Error 18056 can be unwanted noise in certain scenarios

(Indexed 2010-05-05):

I saw a lot of hits on the web when I searched for the Error message 18056 with State 29. I even saw two Microsoft Connect items for this issue filed for SQL Server 2008 instances:http://connect.microsoft.com/SQL/feedback/ViewFeed...(truncated)... http://connect.microsoft.com/SQLServer/feedback/details/5400...(truncated)... I thought it was high time that we pen a blog post on when this message can be safely ignored and when it is supposed to raise alarm bells. Before I get into the nitty-gritty...(truncated)...

Europe PASS, a volcano, Live Meeting, and SQL 2008 R2 BPA.

(Indexed 2010-04-23):

What do these have in common? Sounds like a question on Jeopardy. There is some bad and good news as part of this. Bad Adam Saxton and I were not able to travel to Europe this past week to speak at Europe PASS. A little volcano in Iceland got in the way. We tried our best even securing a flight to Barcelona to hopefully get there by train but those flights eventually got canceled as well. Good We were able to use the wonders of Live meeting and a webcam to successfully present our p...(truncated)...

AWE Allocated Values Reported Incorrectly (Large or Negative Value)

(Indexed 2010-04-21):

I ran into an issue today that is documented but you have to know where to find it so I wanted to point it out. In the middle of a lengthy KB article # 907877 (http://support.microsoft.com/kb/907877) the the following comment. "In a NUMA-enabled system, this value can be incorrect or negative. However, the overall AWE Allocated value in the Memory Manager section is a correct value." I received this output from an customer today. When I investigated it I found a known issue th...(truncated)...

How It Works: Orphan DTC Transaction (Session/SPID = -2)

(Indexed 2010-04-20):

It looks like it would be a good post to help clarify that -2 does NOT mean ORPHAN. ____________________________________________________________...(truncated)... CURRENT EXCHANGE ____________________________________________________________...(truncated)... From: Robert Dorr Sent: Tuesday, April 20, 2010 8:47 AM Subject: RE: ONSITE:Orphaned Distributed Transactions Let me clarify the term Orphaned. A -2 is not Orphaned it means there are NO ENLIS...(truncated)...

An important change for the Microsoft Lifecycle Support Policy.

(Indexed 2010-04-13):

Over the past few months Ive filed some posts on our blog regarding our support lifecycle policies because I know sometimes this topic can get very confusing: http://blogs.msdn.com/psssql/archive/2010/02/17/mainstream-v...(truncated)... http://blogs.msdn.com/psssql/archive/2010/01/08/important-sq...(truncated)... One of the key points in these blogs is that when a service pack for a product hits its end of life date you must upgrade to a newer service pack to get technical support from Micros...(truncated)...

The case of the additional indexes

(Indexed 2010-04-11):

I was assisting with a SQL Server performance issue the other day. The issue was transactional replication was unable to keep up while trying to replicate data from a transactional database to a reporting database. This was causing the customer to miss their data latency SLAs. The oddest part of the problem was that replication to a test reporting database was perfectly able to keep up. Since the CPU, I/O, and memory capabilities of the two servers were similar, we began ...(truncated)...

How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes

(Indexed 2010-04-02):

There seems to be some semantic(s) confusion on the books online description of SOFT NUMA. The area of confusion is from the SQL Server 2008 Books Online section, shown below. Soft-NUMA SQL Server allows you to group CPUs into nodes referred to as soft-NUMA. You usually configure soft-NUMA when you have many CPUs and do not have hardware NUMA, but you can also use soft-NUMA to subdivide hardware NUMA nodes into smaller groups. Only the SQL Server schedule...(truncated)...

How to troubleshoot database corruption errors and System Center

(Indexed 2010-04-01):

What do these two subjects have in common? The SQL Server product team is currently developing an update to the SQL Server Management Pack specifically designed for System Center Operations Manager 2007 SP1. There are several enhancements and fixes in this update to the SQL Management Pack and it is due to release to the web in Q3 of this calendar year 2010. This team approached myself and others in CSS and asked us to review what database corruption errors were being alerted as part of the man...(truncated)...

How It Works: Bob Dorr's SQL Server I/O Presentation

(Indexed 2010-03-24):

I put a presentation together quite some time ago going over various SQL Server I/O behaviors and relating them to the SQL Server I/O whitepapers I authored. I keep getting requests to post the presentation and the information is relevant from SQL 7.0 to SQL Server 2008 and beyond. Here are the RAW slides and my speaker notes. You can read the reference materials outlined in details on the final sides for completeness. [Image] The orig...(truncated)...

Unable to load CLR assembly intermittently

(Indexed 2010-03-23):

Recently, I worked with a customer on an CLR assembly loading issue.Intermittently, they would receive the following error. Msg 10314, Level 16, State 11, Line 1An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about thi...(truncated)...

It helps to read the Whats New once in a while

(Indexed 2010-03-16):

In the course of my job, I use ADPlus (a command-line tool that ships with Debugging Tools for Windows) to capture hang dumps on a regular basis. For both low and high CPU scenarios, I generally need 2-3 hang dumps spaced out over about 10 minutes. Historically, I have always given customers the command-line below and asked them to run it 2-3 times over 10 minutes: adplus hang pn someprocess.exe o somefolder Most of the time, customers follow the instructions. However, someti...(truncated)...

What SPN do I use and how does it get there?

(Indexed 2010-03-09):

This month has turned into another Kerberos Month for me. I had an email discussion regarding SPNs for SQL Server and what we can do to get them created and in a usable state. I thought I would share my response to the questions as it will probably be helpful for someone. Here was the comment that started the conversation. And, by the way, this was actually a good question. I actually see this kind of comment a lot in regards to SPN placement. Not necessarily ...(truncated)...

How It Works: SQL Server 2008 NUMA and Foreign Pages

(Indexed 2010-02-23):

I have received numerous questions about 'foreign pages' so I have put this post together to answer the questions. The SQL Server buffer pool goes through three (3) distinct states. Initial / Ramp-up This state is established during buffer pool initialization and only transitions once committed buffers reaches the target. Once Committed >= Target the state is transitio...(truncated)...

TCP Offloading again?!

(Indexed 2010-02-22):

I have spent probably hundreds of hours on cases involving TCP Offloading and I know most of the signs (intermittent dropped connections, missing traffic in network traces). However, I have to admit I got burned by it the other day and spent several more hours working an issue than I should have. I was working on a server-down case for a financial trading company (in other words, large dollars involved every minute they were down) where the customer was experiencing slow connections to SQ...(truncated)...

Did you start your SQL Server Engine correctly?

(Indexed 2010-02-19):

Often we run into situations where there is a need to add a trace flag or a startup parameter other than the default startup parameters that SQL Server uses. What you may notice sometimes is that even after adding these parameters/flags, the desired behavior from those parameters/flags does not occur. We will use the following parameter and trace flag to demonstrate the reasons but this applies to any combination of trace flags and parameters. "-g " This is used to expand the Mem-To-Leave r...(truncated)...

Microsoft CSS once again a big part of SQL European PASS Summit

(Indexed 2010-02-17):

Im very excited to be invited back to speak at the SQL European PASS Summit 2010 being held in Neuss, Germany April 21-23rd 2010. This is my 4th PASS Summit to speak in Europe and my 3rd year in a row to speak at the location in Neuss. I always enjoy this conference because of the ability to meet many people in a more 1:1 setting. As in past years, Ill be presenting a day-long Pre-Conference Seminar. But unlike past years, Ill be presenting a new topic I have not done at the previous US PASS C...(truncated)...

Mainstream vs Extended Support and SQL Server 2005 SP4: Can someone explain all of this?.

(Indexed 2010-02-17):

Based on community feedback (and believe me it was heard loud and clear), the SQL Server product team announced it will ship one last service pack for SQL Server 2005, SQL Server 2005 Service Pack 4 in the last part of this calendar year 2010. The announcement, found at http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/...(truncated)... also mentions that SQL Server 2008 SP2 will also ship in the 3rd quarter of calendar year 2010. At minimum, SQL Server 2005 SP4 will include all cumul...(truncated)...

How to have a Select All Multi-Value Cascading Parameter

(Indexed 2010-01-29):

Ive seen several cases that involved Multi-Value Cascading Parameters and how the behavior of those work. Let me start with the default (Out of the box) behavior. The example report Ill be using will make use of the AdventureWorksDW sample database. I will also be using SQL 2008 R2 November Release, but the same will apply to SQL 2008 Reporting Services as well. In my report, I have three multi-value parameters. Each one dependent on the parameter before it (cascading pa...(truncated)...

How to get a x64 version of Jet?

(Indexed 2010-01-21):

We have had a number of people ask about how they can get the Jet ODBC driver/OLE DB Provider as 64 bit. Windows only ships the 32 bit versions of these. The answer is that the windows versions wont be x64 as those items are deprecated. What does deprecated mean? Here is the excerpt from the MDAC/WDAC Roadmap on MSDN: Deprecated MDAC/WDAC Components These components are still supported in the current release of MDAC/WDAC, but they might be removed in future release...(truncated)...

High CPU after upgrading to SQL Server 2005 from 2000 due to batch sort

(Indexed 2010-01-11):

We have had some customers who reported to us that their queries consumed more CPU in SQL Server 2005 after they upgraded from 2000. When you examine the plans. Both are very similar. But SQL Server 2005 has something extra. It has "OPTIMIZED" keyword. Here is an example plan segment: |--Nested Loops(Left Outer Join, OUTER REFERENCES:([B].[id_Table02Binding], [B].[si_HashBucket], [Expr1113]) OPTIMIZED WITH UNORDERED PREFETCH) |--Filter(WHERE:([testDB01].[dbo].[Tab...(truncated)...

Important SQL Server and Windows End of Support Dates you should know about.

(Indexed 2010-01-08):

There are some important dates about the end of support for specific products and releases involving SQL Server and Windows that I want you to be aware of: Important Upcoming SQL Server Support Dates 1) SQL Server 2005 Service Pack 2 support ends next week January 12, 2010. You should upgrade to SQL 2005 SP3 or SQL 2008 SP1 immediately. 2) SQL Server 2008 RTM support ends on April 13, 2010. You should make plans to upgrade to SQL Server 2008 SP1 soon. A complete list ...(truncated)...

DBCC CHECK commands encounter problems and reports errors

(Indexed 2010-01-08):

Microsoft SQL Server support team received several support incidents from SQL Server Customers who observed a strange issue when executing DBCC CHECK family of commands against their production databases. We investigated this problem and found a resolution to this issue. Here are some of the identifying characteristics of this problem we noticed among all these customers: These issues started happening for these customers in the past few weekso There were no updates applied for SQL S...(truncated)...

Cannot Generate SSPI Context and Service Account Passwords

(Indexed 2009-12-30):

Was working with Keith Elmore on one of our internal processes and he was hitting a Cannot generate SSPI context when trying to connect from Management Studio. I also saw this come up in a double hop situation (IIS to SQL) when I setup a local repro. [Image] We went through the normal check list for Kerberos Troubleshooting, but really that just consisted of validating the SPN in the case of Management Studio as it was a single hop and we were just trying to do a direct connection w...(truncated)...

SQL 2008 - New Functionality to the dm_os_ring_buffers for Connectivity Troubleshooting

(Indexed 2009-12-28):

Hi, I wanted to make everybody aware of this feature in SQL 2008. Are you tired of having to use NetMon to narrow down a connectivity issue with SQL Server 2008 or have to wait for an elusive connectivity error to reoccur? A new ring buffer called "RING_BUFFER_CONNECTIVITY' has been added to the dmv sys.dm_os_ring_buffers in SQL 2008 RTM. This will automatically log server-side initiated connection closures, if you see nothing in the dmv, then most likely the client reset/closed the connection....(truncated)...

DBMail has Suddenly Stopped Working

(Indexed 2009-12-17):

The other day we ran into a strange Database Mail issue here in SQL support. Customers were running into issues where DBMail would suddenly stop working after doing service pack upgrades to their SQL Servers.This was happening on both SQL 2005 and SQL 2008.The error was also unusual in that the word "timeout" could make you believe we were dealing with a performance issue. Here is what you will see in the SQLAgent.OUT log file.2009-11-03 22:57:37 - ? [129] SQLSERVERAGENT starting under Windows ...(truncated)...

How It Works: Are you handling cancels correctly in your SQLCLR code?

(Indexed 2009-12-15):

I was recently developing a set of SQLCLR functions and procedures for an internal project. One of the tests I added to my suite was to cancel the query (attention) and make sure I handled it properly in my .NET implementation. What I found was much more than I expected and it is something that every SQLCLR developer should understand. When a query is cancelled that is currently executing in SQLCLR an System.Threading.ThreadAbortException exception is raised....(truncated)...

InvalidReportParameterException with Data Driven Subscription

(Indexed 2009-12-10):

Worked on a case yesterday where the customer was getting errors when running a Data Driven subscription. [Image] In the RS Log, we saw something similar to the following: library!WindowsService_15!950!12/10/2009-10:49:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidRep...(truncated)... Default value or value provided for the report parameter 'Param1' is not a valid value., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidRep....(truncated)...

High CPU with Reporting Services 2008

(Indexed 2009-12-09):

Ive run into two cases which fell down the same path and figured we should have some information out there regarding it. The issue was that the customers instance of Reporting Services would just spike to 100% CPU usage. During this time the end users did not notice any interruption in service. This occurred because Reporting Services was trying to unload an AppDomain and it timed out. The timeout that occurred ended up putting us into an infinite loop within the CLR ThreadPool Manag...(truncated)...

Report Builder and Firewalls

(Indexed 2009-12-03):

We have had a few customer calls come in on this scenario that I thought this needed to be documented a bit. Scenario: [Image] In this scenario, the customer has a data source defined on the Report Server. Some were using Named Instances, others were using a Default Instance for the Data Source. There are some aspects of Report Builder that will run server side (from the context of the Report Server). For example, DataSource retrieval and preview of a report. This is assum...(truncated)...

Timeout when deploying SQL CLR objects from Visual Studio 2005 or 2008 but not from Management Studio

(Indexed 2009-12-02):

We have had a few customers who want to deploy their assemblies using Vistual Studio. They encountered various errors similar to below. But when the use SQL Server management studio to manually CREATE ASSEMBLY, everything works fine.Error: starting database upload transaction failed.Error: The operation could not be completed Deploying file: TEstAssembly.dll, Path: E:casesCLR.MYTEstAssemblyTEstAssemblyobjDebugTEstAssembly.dll ... Error: Timeout expired. The timeout period elapsed prior to c...(truncated)...

How It Works: SQLIOSim CTRL+C vs Close Window

(Indexed 2009-12-01):

Today I had an interesting question about the CTRL+C handler for the SQLIOSim console execution. After some investigation I found that a design change request is in order as the CTRL+C handler has a few holes- (I am working on that part). The CTRL+C handler does not prompt you for something like 'Do you wish to terminate the test (Y/N)?'. Instead the first time the CTRL+C is encountered a message is posted to all simulation threads indicating that the test should b...(truncated)...

Doctor, this SQL Server appears to be sick.

(Indexed 2009-11-24):

I thought the PASS Summit was a great success this year. I always enjoy speaking at the PASS Summit (this year I spoke on Wait Types. Im working now on starting to populate the Wait Type Repository) but I also really enjoy working at the SQL Server Clinic. This year CSS combined its efforts with the SQLCAT team. As part of that effort, I though you might want to see a sampling of some of the questions I received and the solutions provided for them: 1. What is the EXECSYNC wait type? A customer...(truncated)...

Unable to run SQL CLR procedure with System.Security.SecurityException

(Indexed 2009-11-20):

Recently, we have troubleshoot a customer issue related to SQL CLR. The problem is that the assembly is configured to use external_access but he kept getting an exception similar to this (this is a sample call stack):Msg 6522, Level 16, State 1, Procedure sp_test, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_test": System.MethodAccessException: Test..ctor() ---> System.Security.SecurityException: Request for the permission of type 'System.Secur...(truncated)...

SQL Server 2005 Cumulative Update or GDR fails when trying to rename the mssqlsystemresource files

(Indexed 2009-11-20):

Ive seen a few customers run in to this recently when the resource database is in a different location than the master database. A cumulate update or GDR for SQL Server 2005 may fail with the following for the SQL Server Database Services 2005 in the Summary_%.txt log file. Look for the latest summary_%.txt file in the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix folder. ******************************************************...(truncated)... Product ...(truncated)...

How It Works: Controlling SQL Server memory dumps

(Indexed 2009-11-17):

I just completed a lengthy bit of research and thought I would share it with everyone. There are several trace flags and registry keys outlined in this post. !!! As always USE WITH APPROPRIATE CAUTION !!! From: Robert Dorr Sent: Tuesday, November 17, 2009 3:23 PM Subject: RE: SQL Server 2008 Trace Flag It is a dump trigger that is enabled for error 5243. (You usually do ...(truncated)...

How It Works: How many databases can be backed up simultaneously?

(Indexed 2009-11-13):

Previously I have posted details on backup/restore designs and how to tell what backup/restore is doing. http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works...(truncated)... http://blogs.msdn.com/psssql/archive/2008/01/28/how-it-works...(truncated)... http://blogs.msdn.com/psssql/archive/2008/01/23/how-it-works...(truncated)... This post attempts to bring the concepts together to answer the question: "How many databases can be backed up simultaneously?" [Image] Th...(truncated)...

The SQL Server Wait Type Repository

(Indexed 2009-11-03):

As part of my talk at the 2009 US PASS Summit here in Seattle called Inside SQL Server Wait Types, Im creating this blog post as a reference point that can be used to find out information about wait types in SQL Server 2005 and 2008. My hope is that if you have a question about a wait type you encounter with SQL Server 2005, 2008, or beyond, you will use this blog post as the starting point. This blog post should also be used as an enhancement to what you can find at the following SQL Serv...(truncated)...

Reduce locking and other needs when updating data - Better Performance

(Indexed 2009-10-26):

The following pattern typically stems from an old practice used in SQL 4.x and 6.x days, before IDENTITY was introduced. begin tran declare @iVal int select @iVal = iVal from CounterTable (HOLDLOCK) where CounterName = 'CaseNumber' update CounterTable set iVal = @iVal + 1 where CounterName = 'CaseNumber' commit tran return @iVal This can be a dangerous construct. Assume that the query is cancelled (attention) right after the select. SQL Server treats t...(truncated)...

Should I have a SNAC with my cumulative update?

(Indexed 2009-10-23):

Here is a question we have received from several customers and internally at Microsoft: Do I need to apply the SNAC package that comes with some of the cumulative updates? Just so we are all on the same terms, SNAC refers to SQL Native Client. and basically contains our SQL Server native code data providers including OLE-DB and ODBC. To understand more why people have asked this question, lets look at a Cumulative Update 4 for SQL Server 2008 SP1 as found at: http://support.microsoft.com/kb/9...(truncated)...

Reporting Services and the MS09-062 GDR (GDI+)

(Indexed 2009-10-15):

Brian Hartman did a great job outlining what the GDR is and why we had it. You can read it here. Im not going to rehash that here. What I wanted to do is outline some of the scenarios involving the GDR. The main thing to be aware of is Windows 2000. Read the section The transition in Brians blog. Scenario 1: In this scenario, we have a Windows 2000 Client using Reporting Manager to render the report. The Report Server does not have the GDR applied. The...(truncated)...

Microsoft CSS is back at PASS again

(Indexed 2009-10-07):

In little under a month (November 3-6), the US PASS 2009 Summit is being held in Seattle, Washington at the downtown Seattle Convention Center. The Microsoft CSS team has been speaking and working at PASS since 2003 and this year we are back again. i thought I would share with you our involvement at this years conference as you may be thinking about whether you should attend or if attending what presentations and activities you should go to: Pre-Conference Seminar This year we only have 1 pre-...(truncated)...

Reporting Services: Whats my version?

(Indexed 2009-09-29):

I was working on a case where I needed to try something on different Cumulative Updates (CU) to see what the result was in each. Let me outline the version numbers for the Service Pack 1 release we have to date. Release Version Number SQL 2008 RTM 10.00.1600 SQL 2008 SP1 10.00.2531 SP1 Cumulative Update 1 10.00.2710 SP1 Cumulative Update 2 10.00.2714 SP1 Cumu...(truncated)...

Did your backup program/utility leave your SQL Server running in an squirrely scenario?

(Indexed 2009-09-23):

My colleges asked me if 'squirrely' is a technical term and for this post the answer is yes. CSS is not going to deny support to customers but SQL Server was not tested in this scenario so you may have chased yourself up a tree, hence I use the term squirrely. SQL Server 2005 introduced snapshot databases and modified DBCC to create secondary snapshot streams for online DBCC operations. The online DBCC creates a secondary stream of the database files that is SPARSE. CSS ...(truncated)...

If you use linked server queries, you need to read this.

(Indexed 2009-09-22):

If you use the linked server feature with SQL Server 2005 and 2008, please read through this carefully. We have discovered several problems that can result in memory leak(s). The conditions are a bit complicated so let me try to describe the problems, how you could be affected, and what action(s) you can take. SQL Server 2008 ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak...(truncated)...

You may not see the data you expect in Extended Event Ring Buffer Targets.

(Indexed 2009-09-17):

Not sure how many of you have started using the new Extended Events feature of SQL Server 2008. For those who have not, there are several good resources out there already for you mostly done by the community: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/2...(truncated)... http://sqlblog.com/blogs/adam_machanic/archive/2009/04/22/te...(truncated)... http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx I also have a few blog posts on our CSS blog that may interest you...(truncated)...

How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

(Indexed 2009-09-17):

The ring buffer records (which can be sent to XEvent) for Resource Monitor and Memory Broker are the key aspects to understanding RM. The record is produced when a change is detected in state monitored by RM. CREATE EVENT SESSION RingBufferInfo ON SERVER ADD EVENT sqlos.resource_monitor_ring_buffer_recorded, ADD EVENT sqlos.memory_broker_ring_buffer_recorded ...(truncated)...

Fun with Locked Pages, AWE, Task Manager, and the Working Set

(Indexed 2009-09-11):

I realize that the topic of locked pages and AWE can be confusing. I dont blame anyone for being confused on this. I also realize we have blogged and talked about this topic many times perhaps beating it to death. And I certainly know this is not really fun to anyone(but it made for a catchy title). But I still get questions both from customers and internally within Microsoft about these topics for both 32bit and 64bit SQL Server systems. So I thought a blog post that summarizes and clarifies a ...(truncated)...

The Future of SQL Server Express Installation and Voting with Microsoft Connect

(Indexed 2009-09-08):

The SQL Product team that owns setup is looking to improve the SQL Express installation experience including application developers who look to embed SQL Server Express with the install of their application. Peter Saddow is a Program Manager on this team and he and I go way back (Peter was the primary owner of hotfix releases way back in the 7.0 days when I first met him). Peter has created two URL links for you to complete a survey to help shape feedback for SQL Server Express Setup for SQL Se...(truncated)...

Another update for the Support Policy for Virtualization for SQL Server.

(Indexed 2009-09-03):

We now have live an update to the KB article for the Support policy for virtualization for SQL Server at: Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment I created this blog post, because there are some changes to the article I believe are important to our SQL Server customers: Added in support for Windows Server 2008 R2 with Hyper-V and Hyper-V Server 2008 R2 Reformatted the article in several places to (hopefully) reduce quest...(truncated)...

Distributed Queries Remote Login Permissions and Execution Plans

(Indexed 2009-09-02):

Recently I troubleshooted an issue where a distributed query was randomly getting a poor plan and slow performance where the join would not be remoted. However, based on the actual data distribution and the join condition, you would expect the join to be remoted. All of the remote servers were SQL Server, so we were not dealing with heterogeneous data sources. Further investigation in to the plan also showed that there were some unexplained estimations higher up in the plan. T...(truncated)...

Come on 64bit so we can leave the mem.

(Indexed 2009-08-26):

I recently saw a question the other day about some errors that indicate memory pressure for the SQL Server Engine and how it might be related to the infamous MemToLeave for SQL Server. The problem with this situation was that the errors were happening on a 64bit version of SQL Server. SoI thought it made sense to make sure and dispel any questions or myths about MemToLeave and its relationship to 64bit SQL Server. Ready for the big answer? There is no MemToLeave for the 64bit version of the SQ...(truncated)...

SSMS Fails to restore with SQL 2000 instances and SSMS Versioning Explained.

(Indexed 2009-08-21):

I recently have been on vacation. When I returned I noticed an interesting issue that deserves some details and explanation in case you encounter the problem. As part of this explanation, Ill give you some information about how the version of SQL Server Management Studio is determined. A problem was recently discovered when using SQL Server Management Studio (SSMS) for SQL Server 2005 or 2008 to restore a SQL Server 2000 database backup. This problem only occurs if you use SSMS to restore the S...(truncated)...

High CPU consumed by Resource Monitor due to low virtual memory

(Indexed 2009-08-20):

We have had a few customers who hit the issue where Resource Monitor consumed a significant portion of CPU in SQL Server 2008. This is a known issue that has been fixed in SQL Server 2008 RTM CU 6 (http://support.microsoft.com/kb/968722/). Next CU release of SQL Server 2008 SP1 will also include a fix.We have only got reports on 32 bit Servers. The root cause is that something like extended procedure, or COM (loaded by sp_OACreate) exhausted virtual address space of SQL SErver. SQL Serv...(truncated)...

SQL Server Cumulative Update or Service Pack Fails With - CREATE DATABASE failed

(Indexed 2009-08-18):

Here is a situation where a cumulative update patch can fail if the default database path for the data and/or log either does not exist, or there is a permissions issue. This example is based on a SQL Server 2005 Cumulative Update 4 for SP3. The problem can happen for any SQL Server 2000, SQL Server 2005 or SQL Server 2008 Service Pack, Hotfix or Cumulative Update. You can use the same methodology in this example to identify if this is the problem and resolve. The summary log ...(truncated)...

Why doesn't SQL Server use index seek with this query?

(Indexed 2009-08-05):

Lately we had a customer who has a query with left outer join. From looking at the indexes and statistics and filtering conditions, our instinct tells us that SQL Server should have used index seek. But supprisingly, SQL Server uses index scan. The table has significant number of rows. This has caused high CPU consumption.In order to illustrate the issue, I came up smaller repro.create table t1 (ItemKey int, t1_name varchar(50))gocreate table t2 (ItemKey int, t2_name varchar(50))gocreate cl...(truncated)...

SQL Server Setup prompts with The installed product does not match the installation source(s). (Part I)

(Indexed 2009-07-23):

Recently I looked at an issue when upgrading from SQL Server 2005 to SQL Server 2008 and in the middle of the upgrade it would halt asking for a matching source for VSS Writer. However, there is nothing descriptive to tell you what specific source it is that you need to choose to find the right match. The customer that inspired this blog post had browsed over and over to what they figured was the source, but they kept getting prompted until they were finally forced to cancel an...(truncated)...

PRB: RML Utilities OStress QryBindColumnStorage: unknown/unhandled SQL datatype

(Indexed 2009-07-23):

Summary During OStress replay you encounter an error like the following and processing stops: 07/21/09 08:05:39.697 [0x00001C0C] QryBindColumnStorage: unknown/unhandled SQL datatype (-152), column number 25 (YourColumn) 07/21/09 08:05:40.697 [0x00001C0C] [spid 75] Encountered previous error condition, processing stopping for file C:Program FilesMicrosoft CorporationRMLUtilsMyReplay.sql 07/21/09 08:05:40.697 [0x00001C0C] [spid 75] Connection Summary (trace-time spid -99) Additionally...(truncated)...

Using DateDiff can query performance problems in SQL 2005 and 2008

(Indexed 2009-07-17):

Lately, we have a customer who reported that a query used to run 20 minutes in 2000 now run serveral hours in SQL 2008. After examining the plan, we discovered that the estimates were much more off in SQL 2008. Specifically, the query has a where condition like (DateColumn< DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())))First of all, if you have a simple expression involving constant and DateAdd like (dateadd(month, -6, '2009-01-01')), SQL Server can optimize it and replace it with a...(truncated)...

Using DateDiff can cause query performance problems in SQL 2005 and 2008

(Indexed 2009-07-17):

How To Test Drive SQL Server Clustering For Free

(Indexed 2009-07-15):

I imagine that many of you are like me when it comes to purchasing software. I want to take a test drive (evaluate) it before I buy it to make sure it really meets my needs. You don't make a automobile purchase by just reading reviews you take it for a test drive. Last week I spent some time determining how one could test drive SQL Server clustering without spending any money. Warning: This information is all based on Microsoft evaluation software.&#...(truncated)...

How far can you push Windows?.

(Indexed 2009-07-09):

As a member of technical support, it does seem I see users pushing our software to the limit more than most. But how does Windows really behave when pushed to the limit on resources? Well an excellent series of blog posts is available for you to find out. Start with this link by Mark Russinovich: http://blogs.technet.com/markrussinovich/archive/2009/07/08/...(truncated)... In this blog post, Mark talks about processes and thread but puts in links to his other posts on limits on memory. If you...(truncated)...

Attach of the Clone ( Databases)

(Indexed 2009-07-08):

Sorry Star Wars fans to mislead you. I thought our readers should know about a feature we use quite a bit in CSS called a Clone Database. It is better publically now known as a Statistics-Only copy of the database. Why is this useful? Because it provides CSS a way to reproduce and diagnose query compilation and plan issues without actually having your data or the entire database. This concept goes back several years when my colleague Keith Elmore worked with Lubor Kollar, then a program manager...(truncated)...

Q&A on Latches in the SQL Server Engine.

(Indexed 2009-07-08):

I recently received a request to shed some light on a few advanced questions about latches and SQL Server. These were good questions so I thought the information might be useful to share with the community. Ill provide this information in the form of Q&A as I was asked (Ive paraphrased the questions): Question: What kind of latch does SQL Server use when reading a page from disk? Answer: Anytime you talk about latching and database pages, think in terms of BUF (buffer) latches. So to read a pa...(truncated)...

When in doubt, Reboot!

(Indexed 2009-07-01):

I tend to get quite a bit of Kerberos related cases. These are related across the box, from the Engine, to Reporting Services to just straight connectivity with custom applications. I had one given to me yesterday because the engineer had gone through everything we normally go through and wasnt getting anywhere. The situation was an 8 node cluster with multiple instances across the nodes. These were running Windows 2008 with SQL 2008. One node in particular was having an...(truncated)...

Installing .NET 3.5 Framework for SQL Server 2008 on a Windows 7 / Windows 2008 RC2 Builds

(Indexed 2009-06-24):

The SQL Server 2008 install requires the .NET 3.5 framework. Changes in Windows 7 can prevent SQL Server 2008 from installing the .NET 3.5 framework pre-requisite. Instead you may need to install the framework separately. 1. Download the .NET 3.5 Framework and save the dotnetfx35setup.exe on the local drive. (http://download.microsoft.com) 2. Using Explorer access the File Properties | Compatibility tab and enable compatibility mode for Vista SP1. 3. Run d...(truncated)...

Zero Byte Attachments in Email Subscriptions

(Indexed 2009-06-18):

I had an issue a while back with RS 2000 where we would get a Zero Byte attachment when hitting a Lotus Notes server. This ended up being corrected in KB 872774. So, when I was assigned a case with a similar description, but this time with RS 2005 SP3, my first question was Are we hitting a Lotus Notes Email Server?. The answer was Yes. It also turned out to be affecting every attachment type except for MHTML. When we hit issues with emails, and we suspect the issue to be with...(truncated)...

SQL Server on Windows 7 / Windows 2008 R2 with more than 64 CPUs

(Indexed 2009-06-16):

The release of the Windows 7/Windows 2008 R2 will support more than 64 CPUs. It is also documented that the SQL Server 10.5 (Kilimanjaro - http://news.cnet.com/8301-13860_3-10236936-56.html) will extend the SQL Server Engine to support more than 64 CPUs. Question: What if I am running a version of SQL Server that is not greater than 64 CPU aware? Windows 7 introduces processor groups of up to 64 CPUs per group. It is not common, but it is possible, to have configuration...(truncated)...

SQL Server 2005 Setup Failure (Multi-Core, Tri-Core, )

(Indexed 2009-06-09):

SQL Server 2005 has a startup check that fails on some of the newer CPU socket designs (triple core, tri core, 12 core, ) as outlined in the knowledge base article: http://support.microsoft.com/kb/954835 Continued testing shows that the NUMPROCS workaround documented in the article does not work for all combinations. Today I submitted a change request to add an alternate workaround. 1. Copy the RTM setup to a local drive. (This includes the Server and Tools or install will fail.) ...(truncated)...

SQL Server and Large Pages Explained.

(Indexed 2009-06-05):

I gave a presentation on debugging memory at the recent Europe PASS Summit in April. In the talk, I mentioned that Large Pages would be used by SQL Server if you used trace flag 834. At the conference, Christian Bolton, a well-known MVP from the UK, mentioned to me that he thought he saw messages in the ERRORLOG that referenced large pages but he didnt have the trace flag turned on. At the time, I told him I didnt see how that was possible. Well, Christian, you were not imagining things. The su...(truncated)...

RDTSCTest CPU Speed Output Clarified

(Indexed 2009-06-04):

Starting with SQL Server 2005 SP3 and SQL Server 2008 the RDTSC counter is not used for timing in SQL Server but you may still have occasion to use RDTSCTest. From: Robert Dorr Sent: Thursday, June 04, 2009 4:14 PM Subject: RE: SQL Troubleshooting - RDTSC In the registry the speed is stored but we have found machines where the registry values are not correct. In order to check the current speed you have to do some timing on the CPU and if speed step or ...(truncated)...

Trying to keep up with Version Numbers.

(Indexed 2009-06-04):

I cant begin to tell you how many times I find myself seeing a version number for SQL Server and not knowing exactly what service pack or cumulative update the build belongs to without quite a bit of pain. I have to admit years ago (ok how about a decade ago) we just had to worry about service packs so I practically had these numbers memorized, but when we introduced the cumulative update model, there were too many build numbers to remember. If you happen to have an ERRORLOG file or can query ...(truncated)...

SQL Server TempDB Number of Files The Raw Truth

(Indexed 2009-06-04):

I continue to answer questions about the number of TEMPDB files and trace flag T1118. It seems there are plenty of advice blogs, wikis, articles and other resources. To help clarify this I am going to post my latest e-mail exchange. From: Robert Dorr Sent: Thursday, June 04, 2009 9:08 AM Subject: RE: TempDB / data files per processor It is not just SGAM in SQL 2000 that encountered contention. SQL Server can still heat up the PFS and other alloca...(truncated)...

SQL Server, Lock Manager, and relaxed FIFO.

(Indexed 2009-06-02):

An interesting question came in from a customer a few weeks ago. Did SQL Server change its traditional FIFO method for granting locks in SQL Server 2005 and 2008? Although there is no official documentation (not yet, Ive put in a request to get this changed), the answer to the question is yes. SQL Server 2005 and future versions use a relaxed FIFO method. You can find a few bits of information about this topic on the web: http://blogs.msdn.com/weix/archive/2005/11/22/496000.aspx This is a po...(truncated)...

How It Works: SQL Server Timings and Timer Output (GetTickCount, timeGetTime, QueryPerformanceCounter, RDTSC, )

(Indexed 2009-05-29):

A series of questions related to start time, duration, end time, T-SQL waitfor delay command and others crossed my path again this week. As I was trying to explain it to a fellow engineer and I realized it has become a bit complicated. The problem stems from a lack of a reliable, light-weight, high resolution timer that does not require additional CPU cycles leading to unwanted power consumption. For example, on a laptop running SQL Express you are ...(truncated)...

How It Works: SQL Server Engine Exception Handling

(Indexed 2009-05-26):

I was recently asked how SQL Server handles exceptions in the core code of the engine. I looked around at various references and did not find a concise document.When an exception is encountered in SQL Server error details such as the following are inserted into the SQL Server error log, a mini-dump is captured and saved in the LOG directory along with a .TXT file containing additional information. Then the connection is terminated which initiates a rollback for the active transaction.2009-03-...(truncated)...

How a log file structure can affect database recovery time

(Indexed 2009-05-21):

We frequently come across situations where databases take a long time to recover. A common scenario is where the recovery process has to roll forward or back several transactions for a database after a SQL Server restart. However, you might also see one of the following symptoms which can result in databases take long time to recover (even if there are not many transactions in the log file).1. Startup of database from restart of SQL Server or Backup/Restore or Attach/Detach or from auto close.2....(truncated)...

An update for Standard SKU Support for Locked Pages.

(Indexed 2009-05-19):

I posted in April that we would be releasing cumulative updates for SQL Server 2008 and 2005 to support the concept of Locked Pages for SQL Server Standard. SQL Server 2008 Cumulative Update 2 for SP1 was released yesterday to provide this support. You can download the CU at: http://support.microsoft.com/kb/970315 We have published a KB article that talks about the change in the CU to support Locked Pages for SQL Server Standard: Support for Locked Pages on SQL Server 2008 Standard Edition 6...(truncated)...

SQL Server Support Policy for Failover Clustering and Virtualization gets an update

(Indexed 2009-05-19):

Last October I posted on our updated policy for support of SQL Server in a virtualization environment. This policy is based on a KB article that summarizes our policy. You recall this article is the following: Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment One fairly controversial aspect to this policy was our support (actually non-support is a better word) for guest failover clustering. We didnt support installing SQL Server failover...(truncated)...

Upgrading Windows with RS 2005

(Indexed 2009-05-18):

From an IIS perspective, there were changes between IIS 6 (Windows 2003) and IIS 7 (Windows 2008). Reporting Services 2005 utilizes IIS for the Report Manager and Report Server Web Service. If you had a Windows 2003 Server with Reporting Services 2005 installed and upgraded to Windows 2008 you would encounter the following errors when trying to hit the RS Instance after the upgrade: [Image] When trying to hit the Report Server Virtual Directory directly, we see a 403.1 error:...(truncated)...

How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values

(Indexed 2009-05-15):

I have recently had several questions related to the dbcc memorystatus and associated memory DMV display values for locked pages and single page allocations. After reviewing several outputs and the code I will attempt to summarize my findings. SQL 2008/2005 have memory related DMVs to report the memory distribution: sys.dm_os_sys_info sys.dm_os_sys_memory sys.dm_os_process_memory >sys.dm_os_nodes sys.dm_os_memory_nodes sys.dm_os_memory_clerk...(truncated)...

SQL Server reports working set trim warning message during early startup phase

(Indexed 2009-05-12):

In the previous posts we have discussed the working set trim message A significant part of sql server process memory has been paged out that gets written to the SQL Server Error log. You can find the details about the various parameters that determine when this message is written to the error log in the KB Article. You might be very familiar with this message and the steps needed to troubleshoot and find the root cause of these memory issues when they appear during the regular lifespan of a SQL ...(truncated)...

How Do I Determine Which Dump Triggers Are Enabled

(Indexed 2009-05-11):

_____________________________________________ Subject: RE: dumptrigger question They don't survive service restart. SQL 2000 did not have dumptrigger enabled for all error messages and I think 1105 is an ex_callprint instead of ex_raise so it won't work but I would have to look at the code to be certain. SQL 2005 and 2008 enable many more dumptrigger conditions. Use display to see what triggers are enabled. dbcc traceon(3604) dbcc dumptrigger('display') _____________________...(truncated)...

SSIS 2005 and the .NET Garbage Collector

(Indexed 2009-05-07):

I was troubleshooting an SSIS Out of Memory issue the other day which led us into how the .NET Garbage Collector (GC) works. When debugging it, I found that we were using the Workstation GC with Concurrent GC On (this is the default for .NET applications). The following blog discusses the different flavors of the GC (Workstation with Concurrent GC On, Workstation with Concurrent GC Off and Server GC) Using GC Efficiently Part 2 http://blogs.msdn.com/maoni/archive/2004/09/25/23427...(truncated)...

How to use FileStream feature in an ASP.NET Web Application that uses Forms or Anonymous authentication

(Indexed 2009-05-06):

We have a customer who uses Form based authentication in his ASP.NET application. But he also wants to use FileStream feature. Authentication becomes a challenge. In order for FileStream to work, sql connection needs to use ingetrated security (not SQL Server based login). Here is the post.When you use Anonymous Access and Form based authentication, windows users cannot be impersonated. Therefore, you can't take the windows user from web client to authenticate to SQL Server. Most of the ...(truncated)...

SQL, Powershell, Windows, Oh My!

(Indexed 2009-05-01):

I was in the process of setting up a repro for another Blog Im going to be posting and ran into an unexpected situation. My VM was running Windows 2003 and I had installed SQL 2008. I was then going to upgrade to Windows 2008 Server and came across the following message: [Image] When you install SQL 2008, we will install PowerShell if it isnt already installed. This will block the upgrade to Windows 2008 until you uninstall PowerShell. Also Note that PowerShell can be ...(truncated)...

Why Should I Use Extended Events in SQL Server 2008?

(Indexed 2009-04-30):

You may or may not have heard of a new diagnostic technology in SQL Server 2008 called Extended Events (XEvent). I thought I would post an example of why this technology can do things nothing else we have can when you deploy SQL Server 2008. Someone internally contacted me about a problem they were having with unexplained page splits. They would see their page splits/sec counter in perfmon spike every 15 minutes and could not figure out exactly what queries were causing the problem. While there...(truncated)...

SQL Server, Locked Pages, and Standard SKU

(Indexed 2009-04-24):

I made an announcement today while speaking at Europe PASS 2009 that we will be providing a method for customers using standard SKU for SQL Server 2005 and 2008 to use the Locked Pages in Memory privilege to allow the server to use the AWE APIs. This is often referred to as locked pages for SQL Server to assist with working set trim problems. I wont provide all the details at this time to understand how to use this but I wanted to post this as soon as possible and let you know that the product ...(truncated)...

SQL Server and the Bad CPU

(Indexed 2009-04-21):

From time to time I encounter an issue where the physical CPU is the source of the problem. I hope you never encounter such and issue but I wanted to provide an example to help those that will. Customer started getting frequent dumps from the SQL Server process. Looking at the exception.log in the SQL Server LOG directory the following was repeated 100s of times. Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION writing address 010BC7B5 at 0x010BC7B4 Exception...(truncated)...

Speaking at European PASS 2009.

(Indexed 2009-04-14):

Im leaving this weekend to travel to Europe to speak at the European PASS 2009 Conference in Neuss, Germany. I spoke at this conference last year and was very impressed on how it was organized and run. From a speaker perspective, the setup of the conference was very excellent. One of the best Ive seen. Having also been a speaker at the US Summit for the past several years, there is a distinct difference between the conferences, mostly in number of attendees. But this smaller size conference has ...(truncated)...

SQL Server 2008 SP1 and Cumulative Updates Explained.....

(Indexed 2009-04-09):

NOTE: I've made a change to this blog post to account for the fact that actually 6 fixes in CU4 for 2008 RTM fixes DID made it into 2008 SP1. One of our readers sent me the following questions I think you will find valuable when understanding our recently posted SQL Server 2008 Service Pack 1 and our cumulative updates. Question: Does SQL Server 2008 SP1 contain the hotfixes that were included in Cumulative Update 4. http://support.microsoft.com/kb/968369/ states only the hotfixes of CU 1/3 ...(truncated)...

The 3 Things you Need to Know to Install SQL 2005 on Windows 2008 Cluster

(Indexed 2009-04-08):

Today I was walking a counterpart through installing SQL Server 2005 cluster on a Windows Server 2008 cluster and realized there are three specific key things you need to know when installing SQL 2005 on Windows 2008 cluster. I also saw that it was very helpful to have a few screen shots to quickly familiarize yourself with where to look and find these things on Windows 2008. 1. Validate the cluster before you install. This is a big advantage with Windows 2008 and can save y...(truncated)...

Troubleshooting sp_xp_cmdshell_proxy_account Errors

(Indexed 2009-04-07):

I was attempting to establish the command shell proxy on my local machine and ran into a series of errors. As I looked at them more I decided to file some work items with the SQL dev team to provide better context in the message text. I hope this post will help provide some insight until then. Focusing on the Error code seems to the be best way to attack the 15137 error. Shown here is an invalid password condition because I typed in the wrong password on my first a...(truncated)...

Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems

(Indexed 2009-04-03):

The Microsoft SQL Server Analysis Services support team has seen an increasing number of issues involving errors when attempting to execute queries against or deploy databases to instances of Analysis Services 2005 and Analysis Services 2008 that are configured for Kerberos authentication and running on Windows 2008 Server or Windows Vista. This note provides information regarding the errors that have been reported and investigated. What we've found is that when a client application is running o...(truncated)...

Updated Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems

(Indexed 2009-04-03):

SQL 2005: SQL Server Configuration Manager Error: Connection to target machine could not be made in a timely fasion.

(Indexed 2009-03-31):

There is a bug with the message spelling (fasion instead of fashion) but that is not the focus of this post. [Image] When launching the SQL Server Configuration Manager you may receive this message if the WMI connections and queries do not complete within 30 seconds. For the customer case I just worked the queries returned in 42 seconds but at the 30 second mark the SQL server components had deemed this a timeout, displayed the dialog and posted a WM_CLOSE to the main MMC.exe wind...(truncated)...

SQLDumper unable to generate mdmp files in SQL Server 2008 Failover clusters

(Indexed 2009-03-29):

SQLDumper utility is used by various components of the product to generate and save diagnostic information in the form of mini-dump and other log files. You will normally find the output files [with extension .mdmp, .txt and .log] created by sqldumper.exe in the LOG folder of the specific instance for the Database Engine. Recently while working with a customer we noticed a problem that prevents these important files from getting created and could affect some of you who are managing SQL Server 20...(truncated)...

Parallel Index Creation performance issue

(Indexed 2009-03-18):

Both SQL Server 2005 and 2008 allow you to do parallel index creation. In fact you can specify MAXDOP in create index statement. When you use MAXDOP (let's say 8), you would assume that 8 threads will do equal amount of work and finish the work at the same time.But this is not always the case. We have recently investigated an issue from a customer who complained about index creation being slow. They have a need to create non-clustered indexes every night on a big table that they import d...(truncated)...

SQL Server: What is a COLD, DIRTY or CLEAN Buffer?

(Indexed 2009-03-17):

Sent: Tuesday, March 17, 2009 2:05 AM Subject: what is clean buffer? what is cold buffer cache? I got these terms from DBCC DROPCLEANBUFFERS , please guide me. ================================================================== From: Robert Dorr Sent: Tuesday, March 17, 2009 9:38 AM Subject: RE: what is clean buffer? what is cold buffer cache? A clean buffer is a data page in memory that is NOT MODIFIED. Modified buffers that ...(truncated)...

How It Works: What Are The Event Source Names Used By SQL Server

(Indexed 2009-03-17):

Sent: Tuesday, March 17, 2009 4:24 AM Subject: Event Source changed from MSSQLServer to MSSQLSERVER to all upper case Our different SQL server versions write to the Eventlog in a mixture of upper/lower case. One version writes as Event Source “MSSQLServer” the other one writes all upper case “MSSQLSERVER”, and so on. I have a problem monitoring because my rules for Eventlog Monitoring and Event Collections are case ...(truncated)...

How to fix your SQL Server 2008 Setup before you run setup (Part II)....

(Indexed 2009-03-17):

Last year you might have read my post where I showed you how to patch setup for RTM for SQL Server 2008 before you launch setup. This processing involved installing a Cumulative Update to apply fixes for the SQL Server 2008 Setup Support Files. Well, we will now be expanding this capability with SQL Server 2008 Service Pack 1 to be able to fix other areas of setup with a new method. This feature, called slipstream setup, actually provides much more. You will now be able to apply the setup ...(truncated)...

SQL Server 2008 Ring Buffer Entries

(Indexed 2009-03-13):

Sent: Monday, March 09, 2009 6:20 PM Subject: Ring Buffers in SQL2008 Hi, do you have any documentation/white papers/examples about extended dmv’s or ring buffers in SQL2008? select distinct ring_buffer_type from sys.dm_os_ring_buffers ================================== My Reply ================================== Nothing that I would consider advanced. Each can have multiple message types. ...(truncated)...

How to use computed columns to improve query performance

(Indexed 2009-03-09):

In general, you should avoid applying a scalar function on a column when comparing against another column, variable or constant. Let's use an example. Frequently, we got cases from customers who are not aware of performance implications and do just that. If you have query like below. It poses challanges to SQL Server optimizer. It can't use index on c1 to do any seeks.select * from t where lower(c1) = 'az'.One natural solution is to avoid applying function lower. But what if the column is ca...(truncated)...

SocketException when creating a new Report Server Database

(Indexed 2009-03-05):

We had a customer call up that encountered a problem when trying to create a new database for their Report Server through the Reporting Services Configuration Manager. This is what they saw: [Image] When we clicked on the Error link, we saw the following details: System.Net.Sockets.SocketException: No such host is known at System.Net.Dns.GetAddrInfo(String name) at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6) &#...(truncated)...

Sparse File Errors: 1450 or 665 due to file fragmentation: Fixes and Workarounds

(Indexed 2009-03-04):

You might be familiar with the sparse file problem that Bob Dorr has blogged about in the past. http://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-r...(truncated)... We wanted to update you with the work we have been involved in the past few months. There are several fixes that we plan to release or already released to address the different aspects of this problem. The list provided below summarizes all of the documentation and fix available for you regarding this problem. We will keep ...(truncated)...

How It Works: SQL Server - VDI (VSS) Backup Resources

(Indexed 2009-03-03):

Last week I worked on an interesting case. The issue was a NT Backup against a single volume containing 500 SQL Server databases. It is rare to have so many databases on a single volume but it is possible and we had a case on this very issue. When a VDI backup (BACKUP ... with SNAPSHOT to DEVICE ...) is issues it currently requires 3 threads to complete the backup. The controlling thread that accepts the BACKUP command and 2 helpers to handle VDI completion and met...(truncated)...

Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems

(Indexed 2009-03-03):

I was asked by our support team to help outline performance counter troubleshooting when the counters don't appear, are missing or don’t seem to be working as expected. I agreed to look into it and as you can see from the information below it turned into a much larger effort than I originally planned. (I originally had this and the How It Works post (http://blogs.msdn.com/psssql/archive/2009/03/03/how-it-work...(truncated)... in an e-mail. I have...(truncated)...

How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components

(Indexed 2009-03-03):

I will be posting a troubleshooting companion to this 'How It Works' that outlines the associated troubleshooting steps for SQL Server base performance counter collection issues. This post attempts to highlight the various components of the SQL Server performance counter library so you have the foundational information to better troubleshoot problems in this area of the SQL Server product. Performance Counter Access Paths The HKEY_PERFORMANCE_DATA is retrie...(truncated)...

You encounter error message "The system cannot find the file specified" when attempting to perform backups using sqlvdi

(Indexed 2009-02-26):

Symptoms you encounter: SQL Server 2000 SP4 x86 is installed on a Windows 2003 X64 server. You use x64 backup applications to perform backups of databases. These backup applications use sqlvdi to interact with the SQL Server. You installed the sqlvdi.dll from KB 913100 and the x64 backup applications work fine. Later you install KB 935465 or any of the SQL 2000 QFEs or GDRs released after this fix. After this you will notice the following messages in the Windows Event log when you attempt to per...(truncated)...

SQL Server 2005 and 2008 Setup Failures (Compressed DATA Directory)

(Indexed 2009-02-24):

We are always looking at customer issue reports in order to improve our software and your experience. The SQL Server Setup development team has identified numerous Watson reports of failed setups due to the compression state of the DATA directory. Shown below is a failure I reproduced on my local machine. The DATA directory has been marked for compression but the (master, model, msdb and tempdb) are individual left uncompressed. I did this by starting the ins...(truncated)...

How It Works: Resource Governor 'INTERNAL' Group

(Indexed 2009-02-23):

From: Customer Sent: Friday, February 20, 2009 2:04 PM I have seen sometimes a user connection classified as Internal when I run the following query: USE master; SELECT sess.session_id, cast(sess.login_name as varchar(40)) login_name, cast(Host_Name as varchar(30)) HostName, grps.name FROM sys.dm_exec_sessions AS sess JOIN sys.dm_resource_governor_workload_groups AS grps ON sess.group_id = ...(truncated)...

SQL Server 2005 or 2008 Reporting Error 17883 - Stalled Scheduler Warnings

(Indexed 2009-02-20):

SQL Server 2005 introduced a background worker to flush trace event streams. The trace buffer is flushed when it becomes fully populated but a partially populated trace buffer remains in-memory until events fill the buffer or the background worker flushes the events to the stream. Periodically the background worker wakes up, checks outstanding trace buffers and flushes them as required. A small 17883 trend has surfaced regarding the trace output taking too long and stalling a ...(truncated)...

SQL Server: Is CHECKDB A Necessity?

(Indexed 2009-02-20):

From: SQL Server Customer Sent: Friday, February 20, 2009 5:08 AM I’m often asked by professionals whether CheckDB is still recommended on SQL2k8. SQL is calculating a checksum to avoid physical page corruption since 2k5. However in my experience (starting with SQL 7.0) I’ve never been able to clearly say that CheckDB is not necessary .. Does anyone have a good advice ? _____________________________________________ From: Robert Dorr ...(truncated)...

How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008)

(Indexed 2009-02-19):

I was posed the question today, how do I migrate my SQL Server 2000 DTS packages to a SQL 2005 Server without upgrading them, thus leaving them as legacy DTS 2000 packages within SQL 2005?In other words, how do I copy or move DTS 2000 packages into SQL Server 2005...If you did an in-place upgrade from SQL 2000 to SQL 2005 (right on top of the old instance) this isn't a big deal, because the DTS packages are still there. However, if you aren't doing an in place upgrade, the MSDB database where th...(truncated)...

Searching for Duplicate SPN's got a little easier

(Indexed 2009-02-13):

We get a lot of calls related to Kerberos configuration, and I'm planning to write more about our experiences and troubleshooting techniques for these types of issues across the box (Engine, AS and RS). With Windows 2000/2003 SetSPN had only a few commands associated with it. Switches: -R = reset HOST ServicePrincipalName Usage: setspn -R computername -A = add arbitrary SPN Usage: setspn -A SPN computername -D = delete ar...(truncated)...

How It Works: File Stream Compression with Backup/Restore

(Indexed 2009-02-11):

The question of file stream compression during backup and restore arose last week and makes for a good clarification topic. Shown here is a conceptual layout of a database containing file stream containers. On the left the folders represent the storage of the individual files used when varbinary(max) has the FILESTREAM attribute. The FSAgent is the SQL Server component that presents the file(s) as a varbinary(max) to the rest of the engine. Other parts ...(truncated)...

PRB: RML Utilities - ReadTrace processing fails with "Attempt to use an invalid variant type"

(Indexed 2009-02-11):

Summary ReadTrace processing may unexpectedly fail with the following error: A serious error condition (true == pVariant->FIsUnknownType()) has been encountered. ...(truncated)... Utility Error: Attempt to use an invalid variant type ...(truncated)... File: .rpcbinary.cpp ...(truncated)... Li...(truncated)...

RML Utilities: Recommended SQL Server 2008 updates for ReadTrace processing

(Indexed 2009-02-11):

There have been a couple SQL Server 2008 fixes shipped in recent Cumulative Updates that address issues you may encounter during ReadTrace processing and .RML generation. Here are the updates we recommend if you use SQL Server 2008 for your ReadTrace processing: http://support.microsoft.com/kb/959796 Error message when an application uses ODBC to bind a TVP and a subtype as DAE in SQL Server 2008: "HY010, [Microsoft][SQL Server Native Client 10.0]Function sequence error" ...(truncated)...

How to troubleshoot leaked SqlConnection Objects (.NET 2.0) - Part 2

(Indexed 2009-02-10):

In the last post in this series, we looked at how we can determine that our Connection pool was exhausted. In this post I'm going to go a little deeper into the Internal connection itself and how we can verify if this is a closed or active connection.Dumping out the internal connection objects A connection object in the System.Data.SqlClient namespace consists of two parts:The SqlConnection class that is used by customers codeThe SqlInternalConnectionTds internal class that is used by the pooli...(truncated)...

SQLIOSim: Default Testing Pattern

(Indexed 2009-02-09):

The SQLIOSim knowledge base article outlines the various configuration files in detail. http://support.microsoft.com/kb/231619 - How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem. I still get inquires about what should I run by default. My answer is: Use the default.config.ini but update the following. Number of iterations to 5 ore more Testing iteration duration to 300 seconds or more. I like 600 myself. File...(truncated)...

How to troubleshoot leaked SqlConnection objects (.NET 2.0) - Part 1

(Indexed 2009-02-05):

One of my colleagues, Kamil Sykora, compiled a document that goes through how to troubleshoot leaked SqlConnection objects (from a .NET 2.0 perspective). It was a fairly large document, so I’m not going to post the whole thing. I’m going to split it out over several posts and base the examples off of a custom demo that I have created. A common issue that we often observe is "leaking" connections in a .NET application. While leaking objects is technically not possi...(truncated)...

Why aren't my subscriptions working?

(Indexed 2009-02-02):

I ran across an interesting issue the other day with subscriptions not firing. I had never ran across this before, but once I was done it made sense. It was also a good exercise of walking through how subscriptions work. This may be obvious for some people, but I found it interesting enough to share it here. Subscriptions had been created through Reporting Services (the issue in question was on SQL 2005 SSRS, but could have just as easily been 2008). The status of these ...(truncated)...

PRB: RML Utilities - Reporter errors during report rendering

(Indexed 2009-01-29):

Summary On a sporadic basis you may encounter any one of the following errors while rendering reports in Reporter: Index was outside the bounds of the array. Object reference not set to an instance of an object. Fix The following Reporting Services hotfix resolves this issue: http://support.microsoft.com/kb/959595 More Information Install the hotifx on the machine where you run Reporter. Sarah Henwood | Microsoft SQL Server Escalation Services[Image]

Hot It Works: SQL Server SuperLatch'ing / Sub-latches

(Indexed 2009-01-28):

SQL Server Books Online uses the terms super latches and sub-latch to describe them. For example the SQL Server:Latches performance counter group calls them super latches. The DVM that exposes the super latches use the term sub-latch, sys.dm_os_sublatches. They are the same internal structure simply exposed under separate terms. A latch is a user mode, reader/writer lock structure used by the SQL Sever to protect its internal structures. An example of a...(truncated)...

INF: Multi-Machine Replay Using ORCA and OSTRESS

(Indexed 2009-01-23):

You can place ORCA on a central computer and allow it to control the sequencing from multiple client computers running OSTRESS. To accomplish this you must configure the firewall properly. Note: Be sure to install the same version of the RML utilities on all machines. All the utilities must be from the same hardware architecture. The RML 9.00.0109 release has a bug that does not allow mixing for different client and server types that which will ...(truncated)...

How It Works: Statistics Sampling for BLOB data

(Indexed 2009-01-22):

I worked on an interesting issue today and the outcome deserves a post. The issue was that when auto update statistics executed the query went from 6 seconds to 6+ minutes. select count(*) from tblImageData where blobData is null Turing on the SP-:StmtStarting and SP:StmtCompleted trace events the internal sample query can be retrieved. SELECT StatMan([SC0]) FROM ( SELECT TOP 100 PERCENT SUBSTRING ([logo], 1, 100)++substring([blobData], &#...(truncated)...

PRB: RML Utilities - ReadTrace and how to workaround MARS

(Indexed 2009-01-21):

Summary We've lately gotten several questions from users regarding ReadTrace and how to workaround when your input trace files contain MARS (Multiple Active Result Sets). ReadTrace currently does not support processing MARS and you will need to use one of the following two workarounds to continue processing. If you try to process a trace file that contains MARS you will see the following message output by ReadTrace: ERROR: SPID was detected using Multiple Active Result Sets (MARS).&#...(truncated)...

WMI Provider Error Access is denied - while changing SQL Server account password

(Indexed 2009-01-21):

Recently one of our blog readers reported that when they tried to change the SQL Server account password in SQL Server Configuration Manager they were getting "WMI Provider Error Access is denied [0x80070005]". This was happening on two separate Windows Server 2008 machines that were in a workgroup, and the user was logged on as local administrator. [Image] I curiously enough was also having this problem on 3 of my machines (two Vista and one Windows 2008) when I was trying to update my...(truncated)...

How It Works: sys.dm_os_buffer_descriptors

(Indexed 2009-01-21):

Here is a question I was recently asked: " I am counting pages in the buffer pool using 'Select count(*) from sys.dm_os_buffer_descriptors' and I get 6,460 buffers but when when I look at the Buffer Node:Database pages counter it shows 6,599. Why the difference?" WARNING: Be careful using dm_os_buffer_descriptors as it can return 200,000+ rows for just a 1.6GB address space. The DMV is designed to avoid blocking and contention. You...(truncated)...

How It Works: SQL Server Sparse Files (DBCC and Snapshot Databases) Revisited

(Indexed 2009-01-20):

Sarah and I have been approached by several customers wanting to know more about sparse file allocation behaviors. This post attempts to answer those questions. Previous Posts http://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-r...(truncated)... http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works...(truncated)... http://blogs.msdn.com/psssql/archive/2008/02/07/how-it-works...(truncated)... Spare File Allocations The d...(truncated)...

"Error: SharePoint is installed but not configured" when installing the RS Add-in on Windows 2008

(Indexed 2009-01-13):

I ran across an interesting issue when trying to install the Reporting Services Add-in for SharePoint on a Windows 2008 server. We would get the error listed above when trying to install the Add-in. We choose repair on the Add-in component. It got to the point of removing backup files and then we got the following error: Setup was interrupted before Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft Sharepoint Technologies could be completely installed. Unins...(truncated)...

ReadTrace Generates Unexpected Connect Node - Not Really

(Indexed 2009-01-12):

At first glance it appears that ReadTrace has generated an unexpected connect node. Upon closer inspection the behavior is correct in order to maintain the proper set options for the connection. Trace has both physical and logical connect/disconnect events. The logical is indicated with a sub-class value of 2. You can observe the following pattern. Existing Connection - sub-class 1 (Should be output as connect node) RPC Events SQL:Batch Events ...(truncated)...

Filtering Trace Data Without Loading In Profiler or Database Table

(Indexed 2009-01-08):

We are often asked how to extend the filtering of RML (ReadTrace/OStress) or Profiler. Filtering during trace production is the preferred method because it typically reduces the overall impact of the Trace on the SQL Server throughput. For late filtering you can generally use the Profiler filtering or database table (fn_trace_gettable and save to .TRC using Profiler). However, the SMO object model provides TraceFile implementations that make this very easy. The Tr...(truncated)...

Installing SQL Server 2005 SP3 or SQL Server 2008 on builds prior to XP SP3 Can lead to Bug Check (0xF4, Blue screen, Critical Process Termination)

(Indexed 2009-01-07):

Due to a bug in Windows XP builds prior to the XP SP2 hot fix or XP SP3 release you may encounter one of the following bugs while attempting to install SQL Server 2008 or SQL Server 2005 SP3 based builds. An application that uses the Sxs.dll file crashes when you run the application on a Windows XP-based computer http://support.microsoft.com/kb/943232 The computer may restart when you add a manifest that has the Windows Vista extension to an .exe file...(truncated)...

WMI Provider Error: Access is denied. [0x80070005] from SQL Server Computer Manager

(Indexed 2009-01-05):

When you attempt to change the Login Account for the SQL services [SQL Server, SQL Agent, Full Text, Analysis Services] from the SQL Server Computer Manager tool, you may encounter the following error message:"WMI Provider Error""Access is denied. [0x80070005]" This post is applicable for SQL Services installed as failover instances on Windows Clusters.If this is the only operation [changing service startup account] that gives you this error and you can perform all other operations from Compute...(truncated)...

SQL 2005 JDBC Driver and Database Mirroring

(Indexed 2008-12-31):

We ran into some interesting situations with the SQL 2005 JDBC Driver (v1.2) and it's use with failover partners. Take the following connection string: jdbc:sqlserver://myserver1;databaseName=AdventureWorks;failo...(truncated)... In this connection string, our Primary server will be myserver1 with our failover server being myserver2. If the primary server becomes unresponsive, we will fail over to the myserver2. This connection string should work perfectly fine. Lets look at anoth...(truncated)...

Failure to load Report Manager due to truncated UserAgent string

(Indexed 2008-12-29):

We came across an issue when trying to load Report Manager. We would get the following exception: System.ArgumentException: Version string portion was too short or too long. at System.Version..ctor(String version) at System.Web.Configuration.HttpCapabilitiesBase.GetClrVersions() at System.Web.Configuration.HttpCapabilitiesBase.get_ClrVersion() at Microsoft.ReportingServices.Common.RBRequirements.get_Client...(truncated)... at Microsoft.ReportingServices.UI.FolderI...(truncated)...

Using SQLIOSim to Diagnose SQL Server Reported Checksum (Error 824/823) Failures

(Indexed 2008-12-19):

This is an extension to by previous post about SQLIOSim data integrity testing. http://blogs.msdn.com/psssql/archive/2008/03/05/how-it-works...(truncated)... SQLIOSim (.exe/.com) is the predecessor to SQLIOStress. SQLIOSim is SQL Server independent utility, generating both SQL Server and random I/O patterns. Demonstration [Image]SQLIOSim demonstration by rdorr (Larger Video Sizes: http://communityclips.officelabs.com/Video.aspx?videoId=f544...(t...(truncated)...

SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage

(Indexed 2008-12-17):

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. SQL Server 2008 optimized mixed extent allocation behavior reducing the need for trace flag 1118 and the contention on SGAM(s). The logic was also added to SQL Server 2005 in a CU release, KB article 936185. The ...(truncated)...

How It Works: SQL Server No Longer Uses RDTSC For Timings in SQL 2008 and SQL 2005 Service Pack 3 (SP3)

(Indexed 2008-12-16):

Many of you have encountered the RDTSC timing variances that I outlined in an earlier blog post: http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2...(truncated)... and http://blogs.msdn.com/psssql/archive/2007/08/19/sql-server-2...(truncated)... Several years ago SQL Server 2008 builds removed pure RDTSC, microsecond timings and replaced them with an MMTIME like, timing mechanism. The timer (see timeGetTime for similar documentation) removes variances that can...(truncated)...

How It Works: SQL Trace and MARS Connections

(Indexed 2008-12-12):

MSDN, TechNet, Books Online and other sources outline what Multiple Active Result Sets (MARS) is, how to use it and transaction scopes. Working on an issue this week I found I needed to understand what the trace looked like. In doing so I found the following helpful. The trace column (RequestId) represents the logical connection id for a MARS connection. ODBC / ADO / SQLConnection / SQLOLEDB The following, simplistic example, shows HBDC: ODBC connection...(truncated)...

INF: RML Utilities: Turning on Trace Flags

(Indexed 2008-12-12):

Summary When using the RML utilities the logged message or documentation can indicate that use of a trace flag is necessary to change behavior. More Information The RML Utilities use command line parameter (-T) as a trace flag to alter the utilities behavior. The term trace flag is a hold over from the SQL Server concept. Unless otherwise specified the trace flag is NOT a dbcc traceon(##) request. Instead, add the -T## as a parameter to the command line invocatio...(truncated)...

How It Works: What is behind the SQLCLR wait category in SQL Server 2008 Activity Monitor

(Indexed 2008-12-02):

I was asked a question as the CSS First Aid Station at SQL PASS 2008 where the Activity Monitor kept showing the SQLCLR wait category as the top waiter, even when no other activity was going on. After some digging I found that this is a bug and currently marked to be fixed SQL Server 2008 SP1. However, it was an interesting investigation that I would like to share. The activity monitor rolls up wait type categories. Shown here are the sys.dm_os_wait_stats (wait_type) values wh...(truncated)...

Should I run SQLIOSim? - An e-mail follow-up from SQL PASS 2008

(Indexed 2008-11-24):

From: Robert Dorr [mailto:rdorr@microsoft.com] Sent: Thursday, November 20, 2008 11:29 AM To: A SQL PASS MEMBER - CSS First Aid Station Question About SQLIOSim As we discussed at SQL PASS – SQLIOSim is an independent utility and has been used as part of the Windows Hardware Compatibility Testing (WHCL) suite for several years. Many of the major hardware manufacturers use it as part of their release criteria testing as well. ...(truncated)...

RSWindowsNegotiate and 401.1 Error when using RS 2008

(Indexed 2008-11-18):

While I was setting up one of my demos for SQL PASS, I starting hitting 401.1 errors. I was setting up a SharePoint Intergrated setup with Reporting Services. I knew I had a distributed environment, so I accounted for my Kerberos configuration. I lined up my SPNs and made sure my accounts were trusted for delegation. So, I was a little surprised when I was hitting a 401.1 error when trying to run a report or create a new Datasource through the SharePoint RS Library. I was usi...(truncated)...

PRB: RML Utilities - ReadTrace -M or -MF parameters cause processing to fail with SQL 2008 trace files

(Indexed 2008-11-14):

Summary ReadTrace processing may unexpectedly stop with an assertion if you use the -M or -MF parameters with SQL 2008 trace files. Workaround Do not use the -M or -MF parameters with SQL 2008 trace files. More Information This problem is currently being investigated and I will post back with any updates. Sarah Henwood | Microsoft SQL Server Escalation Services[Image]

INF: RML Utilities - You cannot use 9.01 Reporter against performance analysis database loaded by down-level ReadTrace

(Indexed 2008-11-14):

Summary If you use the latest 9.01 release of Reporter and point to a performance analysis database that was loaded by a down-level version of ReadTrace, you may get errors about missing stored procedures such as the following: "Could not find stored procedure 'ReadTrace.usp_CheckSchemaVersion'" More Information The 9.01 Reporter version will not work against a database that was populated by a down-level version of ReadTrace. The 9.01 version of Reporter depends on certain schema chan...(truncated)...

Microsoft CSS at the PASS Summit 2008

(Indexed 2008-11-13):

Once again I'm proud to be part of a team of CSS engineers from Microsoft that will be speaking and participating at the PASS Summit 2008 next week in Seattle, Washington. A nice summary of the CSS participation can be found on the PASS website at: http://summit2008.sqlpass.org/css_agenda.html Let me give you some inside info about what is on the agenda: Pre-Conference Seminars: Day 1: Engine Performance and Advanced Diagnostics The real star of this day is my colleague Keith Elmore. No on...(truncated)...

PRB: RML Utilities - Setup may Always Prompt for Report Viewer 2008 Redistributable

(Indexed 2008-11-12):

Summary In rare circumstances on systems with UAC enabled, setup of Cumulative Update 1 of the RML Utilities may continue to prompt you for the Microsoft Report Viewer 2008 Redistributable (SP1 or later) even after you have already installed the redistributable: “The RML Utilities require the Microsoft Report Viewer 2008 Redistributable (SP1 or later). Do you want to go to the Microsoft download center to install this package?” [Image] Workaround To workaround disable UAC or run...(truncated)...

How It Works: SQLIOSim - Running Average, Target Duration, Discarded Buffers ...

(Indexed 2008-11-12):

The following is from a lengthy conversation I had with Kevin Kline. I believe he will be posting more details along with his "Understanding SQLIOSim Output - http://sqlblog.com/blogs/kevin_kline/archive/2007/06/28/unde...(truncated)... ********** Final Summary for file E:sqliosim.mdx ********** File Attributes: Compression = No, Encryption = No, Sparse = No Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 17, Number of times IO throttled = 90...(truncated)...

Cumulative Update 1 to the RML Utilities for Microsoft SQL Server Released

(Indexed 2008-11-12):

[Image] Version 9.01 of the RML Utilities for Microsoft SQL Server has been released. This release of the RML Utilities provides support for SQL Server 2000, SQL Server 2005 and SQL Server 2008. Additionally this release of the RML Utilities for SQL Server contains important software updates, enhanced features and reports, and performance and scalability improvements. To download the current web release of the RML Utilities for SQL Server visit the following ...(truncated)...

RML Utilities: ReadTrace may encounter assertion building the partial aggregates

(Indexed 2008-11-03):

Summary In rare circumstances ReadTrace may encounter the following SQL Server 2008 assertion error when attempting to execute a parallel query. 11/03/08 00:05:35.139 [0X00003B94] Dumping error record 1 of 2: 11/03/08 00:05:35.139 [0X00003B94] ------------------------------------------------------------...(truncated)... 11/03/08 00:05:35.144 [0X00003B94] Description = A system assertion check has failed. Check the SQL Server err...(truncated)...

SQL Server Support in a Hardware Virtualization Environment

(Indexed 2008-10-08):

There is no doubt that virtualization is a hot and popular topic (the number of questions over email I get daily are a testament to that). Therefore, I think it is important for our customers to understand the support policies from Microsoft regarding SQL Server running in a hardware virtualization environment. We have just published the following KB article that outlines this policy: http://support.microsoft.com/?id=956893 What does this Mean? This article previously only discussed Hyper-V ...(truncated)...

ReportViewer Bug Affects SQL Server Management Studio and Reporter Utility

(Indexed 2008-10-07):

Outer Exception: Object reference not set to an instance of an object, an error occurred during rendering of the report. Inner Exception: System.IndexOutOfRangeException: Index was outside the bounds of the array. [Image] The thread safety issue has been reproduced and Microsoft is investigating a fix. The condition occurs easier on a multi-CPU computer but can happen on a single CPU computer as well. Workaround: Since the issue is time sensitive you can attempt to op...(truncated)...

RML Utilities: ReportViewer Bug Affects SQL Server Management Studio and Reporter Utility

(Indexed 2008-10-07):

Summary An index out of bounds error can occur while rendering a report in Local Report Viewer Mode with more than one dataset. Outer Exception: Object reference not set to an instance of an object, an error occurred during rendering of the report. Inner Exception: System.IndexOutOfRangeException: Index was outside the bounds of the array. [Image] This is thread safety issue and occurs more frequently on a multi-CPU computer but can happen on a single CPU computer as well. Workaroun...(truncated)...

Windows Scalable Networking Pack Possible Performance and Concurrency Impacts to SQL Server Workloads

(Indexed 2008-10-01):

The Scalable Networking Pack aka. SNP (TCP Chimney, RSS and NetDMA) is enabled by default if you apply Windows Server 2003 Sp2. This is an operating system feature that provides capability to offload TCP/IP packet processing from the processor to the network adapters and some other balancing options. (For a full description of this feature see http://support.microsoft.com/kb/912222.) These features have been known to cause issues on SQL Server systems such as general network errors ...(truncated)...

How to fix your SQL Server 2008 Setup before you run setup...

(Indexed 2008-09-30):

Huh? This is not meant to be a brain teaser. The purpose of this post is to help you avoid problems with SQL Server 2008 setup before you run..well setup. Why not take advantage of known fixes we have for setup to avoid the problem in the first place? This would include a new installation, upgrade, or a install of a new instance or feature. In this blog post, I'll give you details on how this works and point you to resources for known setup fixes that might affect you. So let's say you are goi...(truncated)...

Welcome to SQL Server 2008 Patching

(Indexed 2008-09-26):

We have posted the first cumulative update for SQL Server 2008 aptly named Cumulative Update 1 (CU1). I thought before you considered installing a patch for SQL Server 2008, you would like to see a tour of what it looks like compared to SQL Server 2005 patch installations. The first thing I did was download the package for the update from the following KB article: http://support.microsoft.com/kb/956717 This article includes a list of fixes contained in CU1. If you remember, I wrote a blog po...(truncated)...

Reporting Services Windows Service will not start after applying MS08-052

(Indexed 2008-09-23):

We have been seeing a few cases where the Windows Service for Reporting Services will not start after applying the following fix: MS08-052: Description of the security update for GDI+ for SQL Server 2005 Service Pack 2 GDR: September 9, 2008http://support.microsoft.com/kb/954606/ You may also see the following in the Application Log: Event 1 Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7000 The ServiceName service failed to start due to the following er...(truncated)...

How it Works: SQL Command Mode Parsing

(Indexed 2008-09-18):

I ran across a teaser this week that makes for a interesting blog post. SQL Server uses a common batch parser for the SQLCMD, OSQL, SSMS Query Window in Command mode and various other locations. The primary job of this parser is to identify GO delimited batches. However, it can also identify the extended commands as outlined in SQL Server Books Online. Let's use the following .SQL file as our example. The !! (bang bang) executes the specified command. ...(truncated)...

SQL Server (2000, 2005, 2008): Recovery/Rollback Taking Longer Than Expected

(Indexed 2008-09-12):

I have blogged on the various ways to determine the state of recovery but this week I was involved in an interesting discussion around rollback. The transaction has run for 14 hours and then a KILL SPID was issued. The SPID goes into rollback and happed to do so for 2 days and 4 hours. The natural question is why not 14 hours to rollback? It is hard to tell without some specific details but if the original query used parallel workers it could have done more than 14 hours of work. For example ...(truncated)...

Upgrade for SQL Server 2008 can fail if you have renamed the 'sa' account

(Indexed 2008-09-10):

Gail Shaw, a SQL MVP, presented me with a new issue for SQL Server 2008 I had not heard of before. It was posted at the following web site: http://www.sqlservercentral.com/Forums/Topic560965-391-1.aspx This customer had renamed the 'sa' account as many others have done to avoid login attacks on the 'sa' account. An example of a posting that talks about how to do this can be found at: http://blogs.msdn.com/sqltips/archive/2005/08/27/457184.aspx One comment here before I get into the real prob...(truncated)...

SQL Server 2005 Setup Fails in WOW (x86) On Computer With More Than 32 CPUs

(Indexed 2008-09-05):

SCENARIO SQL Server fails to install a WOW64 instance (x86) an on a computer with more than 32 CPUs. During setup you will encounter a SQL Server failed to start message. SQL Server is attempting to determine the NUMA configuration during startup. WOW instances are x86 and limited to 32 CPUs but the x64 installation can have more than 32 CPUs. SQL Server encounters an exception which ...(truncated)...

How It Works: 17888 - All Schedulers on Node Appear Deadlocked

(Indexed 2008-09-03):

Error 17888: All schedulers on Node ## appear deadlocked due to a large number of worker threads waiting on . Process Utilization ##%. SQL Server creates a Scheduler Monitor thread for each node. One of the Scheduler Monitor tasks is to watch the schedulers assigned to the node for progress. Slow progress triggers additional checks; among them is the 17888 check. The 17888 condition is when Schedu...(truncated)...

SQL Server 2008 Management Tools Basic vs Complete Explained....

(Indexed 2008-09-02):

A few questions have come up about the differences between the features you can install on SQL Server 2008 called Management Tools - Basic and Management Tools - Complete. So I thought I'd blog about these to resolve any questions you might have. Related to this are questions I've received on what is SQL Server Management Studio Basic as described in the download pages for SQL Server 2008 Express. When you install SQL Server 2008 editions other than SQL Server 2008 Express, you are presen...(truncated)...

How to Rebuild System Databases in SQL Server 2008

(Indexed 2008-08-29):

In SQL Server 2005, we introduced a different method than in previous versions to rebuild system databases (affectionately known as "rebuild master"). You were required to use the setup.exe program with command line switches. This is no different in SQL Server 2008 but the command line switches have changed some and the process behind the scenes to rebuild the system databases (master, model, and msdb) is also a bit different. Currently the SQL Server 2008 Books Online only mention an option f...(truncated)...

How It Works: Creating An EndPoint Adds An Entry To SysLogins

(Indexed 2008-08-29):

My SQL Server does not have individual windows users established as separate logins. Instead it has the DomainSQLUsers group established as a WINDOWS GROUP login. You can review your mappings using the following DMVs. select * from syslogins select * from sys.server_principals select * from sys.server_permissions When I used the following CREATE ENDPOINT statement the DomainUserName appeared in syslogins and server_principals. CREATE ENDPOINT endpoin...(truncated)...

SQL Server 2008 Express, .Net Framework 2.0 SP2, and 3.5 SP1 explained...

(Indexed 2008-08-28):

Many of you may have read my post about dependencies with Visual Studio 2008 SP1 and the .Net Framework 3.5 SP1 and SQL Server 2008. I have some related information to provide to you about SQL Server 2008 Express. If you are a user of SQL Server 2008 Express, there is some new information you should know about. I created a separate post here because the other post was getting pretty big and this information is something specific to SQL Express SKUs so didn't want it to get lost in the message. ...(truncated)...

Using Locking Like Synchronization Object

(Indexed 2008-08-27):

This post is more of a T-SQL trick and NOT a recommendation practice, but since I addressed this in a recent customer case I thought I would share it. Problem: Application needs to lock a row for the duration of the transaction and ensure no data from the row can be read by another user under read committed isolation. Clearly you can see the downside to concurrency and why I would not recommend this design. However, this customer needed to support an application that was...(truncated)...

Why you should read the Release Notes for SQL Server 2008

(Indexed 2008-08-26):

Do the release notes really ever contain anything that can help you with your deployment of a product? Well in the case of SQL Server 2008, the answer is YES. Here are the following benefits for using the Releases Notes which can be found at the following direct link: http://download.microsoft.com/download/4/9/e/49eeb41a-a769-4...(truncated)... or by simply performing a Live Search of "SQL Server 2008 Release Notes": 1) Details about the dependency on Visual Studio 2008 SP1 in a nice ta...(truncated)...

IsNumeric Returns (1/TRUE) and I Expected (0/FALSE)

(Indexed 2008-08-25):

The string '100,200,300' has fooled many a customer and support engineer alike. select cast('100,200,300' as bigint) Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint. One might argue that this is a bigint value. However, the T-SQL parser does not allow comma separators in a bigint conversion so you get the proceeding error. So why would IsNumeric return a 1 for the value. The simple answer is a money value. Commas are allow...(truncated)...

How It Works: SQLIOSim [Audit Users] and .INI Control File Sections with User Count Options

(Indexed 2008-08-19):

The Page Audit User is one of the main execution paths for data integrity checking. SQLIOSim runs test cycles and during each test cycle the [Audit User] section of the .INI configuration file is used. These workers perform various page audit actives. After the cycle completes SQLIOSim creates audit users, per CPU to perform integrity checks for every page. This might not be possible during the test cycle duration and occurs during a known file state. The [Au...(truncated)...

Timeouts when connecting to Named Instances

(Indexed 2008-08-19):

When connecting to a SQL Named Instance, you may encounter a Timeout error when trying to connect to it if the client is Vista or Windows 2008 with the client Firewall enabled. This particular issue is only present if you are running the SQL 2008 Browser Service.The browser service is what allows us to connect to a SQL Named Instance by using the friendly instance name (i.e. MyServerSqlInstance) as opposed to the server and port (i.e. MyServer,2508). When connecting to a Default Instance, whic...(truncated)...

Is it really the released version of SQL Server 2008?

(Indexed 2008-08-18):

The following is a question we have received from a few folks who were concerned after installing SQL Server 2008 that they did not have the final released build. When you use SQL Server Management Studio and select the Help/About menu choice to see what version you have, you may see something like this: [Image] Notice the Versions column and the string for the first and second "rows". It says 10.0.1600.22 ((SQL_PreRelease)... So what does SQL_PreRelease mean? Don't I have the "released" SQL ...(truncated)...

SQL Server 2008, Visual Studio 2008 SP1, and .Net Framework 3.5 SP1 explained....

(Indexed 2008-08-11):

I must say that I'm personally proud to see our release of SQL Server 2008. I've been involved with the product team on this release since its early inception in December of 2004 so this moment does bring me great satisfaction. I've posted a few entries already on features specific to SQL Sever 2008 that are important to CSS, one of them called Extended Events. I'll continue to regularly post more about the release over the coming months especially leading up to this year's PASS conference in S...(truncated)...

How It Works: Attention, Attention or Should I say Cancel the Query and Be Sure to Process Your Results

(Indexed 2008-07-23):

I ran into this issue the other day and decided it would make a good post. Since I have seen this issue more times that I can count I will again attempt to provide details. The attention event in SQL Server trace output indicates a query cancellation. There are three common ways this occurs. 1. Asynchronous cancellation (ODBC SQLCancel for example) 2. Query Timeout Exceeded and the client driver submits the attention 3. Results are not processed...(truncated)...

SQL Server 2005 Encounters Exception During Install When System has Odd Number of Processors or Logical Processors Per Core

(Indexed 2008-07-23):

While attempting to install SQL Server 2005 you may encounter an error familiar to the following and a retry option presented by setup. Faulting application sqlservr.exe, version 2005.90.1399.0, time stamp 0x434f82e9, faulting module sqlservr.exe, version 2005.90.1399.0, time stamp 0x434f82e9, exception code 0xc0000005, fault offset 0x01019027, process id 0xe70, application start time 0x01c8d28679b262eb. There are a few known issues; all corrected if you install...(truncated)...

Supporting SQL Server 2008: The system_health session

(Indexed 2008-07-15):

In my first post on SQL Server 2008, I introduced a new feature called Extended Events (we like to call this Xevent internally). While we were working with the XEvent development team, it dawned on us that we could perhaps build an event session that was "always-on" (in fact internally we were calling this the "Always-On" session). In this session, we could add some events that were perhaps important for troubleshooting certain problems with the SQL Server Engine. Thus was born the system_healt...(truncated)...

SQL Server reports operating system error 1450 or 1452 or 665 (retries)

(Indexed 2008-07-10):

Overview The error: 1450/1452 insufficient system resources exist to complete the requested service. These are not a SQL Server based errors. The error cause is related to a depleted system resource (non-paged pool, paged pool ,...). These issues have to be tracked down at the operating system level. This involves collection of system level performance monitor counters and evaluating the basic health of the overall system. /3GB A commonly overlooked configuration option is t...(truncated)...

SQL Server I/O Bottleneck, I don't have one, YES YOU DO!

(Indexed 2008-07-08):

The mistake I see people make is when looking at the SQL Server PAGE I/O waits and stalled I/O warnings is when comparing it to the Avg. Disk Seconds/Transfer. Everyone seems to forget that average means average and they look at the value and say I don't see any I/O taking longer than 15 seconds. Take the following example over a 5 minute window. 2000 I/Os at 8ms = 16000ms 1 I/O at 15000ms = 15000 ms TOTAL TIME: 31000ms AVG: 15.49ms &#...(truncated)...

SQL Server, Windows Server 2008, and the Windows Firewall

(Indexed 2008-07-03):

Sounds like a movie title. No, it is actually a topic you need to know about if you plan to install SQL Server 2005 or SQL Server 2008 on Windows Server 2008. Instead of me trying to explain the details I would rather you learn from the experts on this topic. A very good blog post has been created by Shawn Hernan from the security group of the SQL Server Development team to get you started. So,....if you plan to install SQL Server 2005 or SQL Server 2008 (when it releases) on Windows Server 200...(truncated)...

Get Ready for SQL Server 2008

(Indexed 2008-07-01):

Technorati Tags: SQL Server 2008,Extended Events Someone who contributes to this blog posted back in April that after the Europe PASS conference they would start in May creating some posts about SQL Server 2008 New Features especially ones related to diagnostics. I don't know who posted that but it is now July and no posts have been seen (who was that guy?). So I guess I'll step in and start creating these posts given that we are marching towards a release of SQL Server 2008 in Q3 of this year....(truncated)...

How It Works: Trace (.TRC) File Security

(Indexed 2008-06-25):

SQL Server 2000 shipped with different trace file security than later builds of SQL Server 2000, SQL Server 2005 and SQL Server 2008. The SQL Server process (sqlservr.exe) writes the trace files to the specified destination. The SQL Server 2000 release did not explicitly set the ACLs on the file when it was created. The behavior was to allow the file to inherit the permissions of the destination directory. Closer inspection quickly indicates that for security reasons t...(truncated)...

How It Works: More on DBCC Shrink* Activities

(Indexed 2008-06-18):

My peers are starting to tease me about becoming a dbcc shrink* expert. (Ha, Ha I said.) Then, I uncovered another design facet about shrinkfile helping with a SQL Server 2000 case. The situation: dbcc shrinkfile, with EMPTYFILE, against a file with only 128 pages was taking hours to complete. In the end it is by design but it was not a design I anticipated. Here is what is taking place during the shrink of this file. Shrink set...(truncated)...

Helpful Hint: Making Review of a Query Plan Easier

(Indexed 2008-06-17):

I think we all have looked at the output of statistics profile and wished the Estimated Rows and Estimated Executions columns were placed beside the Rows and Executes columns. Well, they can be. In grid mode you simply drag the columns beside each other. If the output is in text mode the Visual Studio Editor provides the ATL+LEFT MOUSE column selection capability. Since SQL Server Management Studio is hosted in this shell you can hold down the ALT key while using...(truncated)...

Query Performance issues associated with a large sized security cache

(Indexed 2008-06-16):

In the past couple of months, SQL Server support team has come across some customers running into performance issues attributed to TokenAndPermUserStore in SQL Server 2005. This blog post attempts to compile all the information we have so far regarding this problem. TokenAndPermUserStore is one of the many caches present in the SQL Server 2005 memory architecture. As the name implies, this cache stores various security related information used by the SQL Server Engine. If you are curious to know...(truncated)...

How It Works: SQL Server 2005 - Possible data recovery when checksum failure reported

(Indexed 2008-05-22):

I and Microsoft will always opt for restore of a backup when corruption of the database is encountered. This is the only way to safely restore the database to a known state. All other actions, such as dbcc repair, can remove data from the database and break referential integrity or other business logic. Whenever you embark on a data recovery effort outside of backup you open up the possibility of further data loss and future application problems due to missing...(truncated)...

SQL Server 2005 SP3 and Self-Service Hotfixes

(Indexed 2008-04-25):

If you have not seen or heard the news, we have announced our plans to release a service pack for SQL Server 2005. If you want to read more about the announcement, look at this blog post http://blogs.technet.com/dataplatforminsider/archive/2008/04...(truncated)... We are excited to see this in CSS because this will be an opportunity for the larger community to install a build that contains fixes to problems already addressed that you might not otherwise have. The plans are to have this ...(truncated)...

How It Works: What is a Sleeping / Awaiting Command Session

(Indexed 2008-04-21):

This issue is as old as SQL Server. In fact, it goes back to Sybase days but continues to fool and puzzle administrators. A session with that status of sleeping / awaiting command is simply a client connection with no active query to the SQL Server. The table below shows the transitions from running to sleeping states for a session. Connect Running Connect Completed Sleeping / Awaiting Command select @@VERSION Running ...(truncated)...

Changes introduced in CU#7 may impact Analysis Services backup and sync operations

(Indexed 2008-04-16):

Cumulative Update #7 for SQL Server includes a fix for Analysis Services backups to correct a problem with truncation of metadata files as described in KB Article 950968. The fix involves changes to the locking levels used for backups, thereby avoiding the truncation issue which could have been encountered previously. Unfortunately, the increased locking levels come at a price. Prior to this fix, a running backup was not treated as a blocking event for a processing commit, and thus would be ...(truncated)...

How It Works: SQL Server Checkpoint (FlushCache) Outstanding I/O Target

(Indexed 2008-04-11):

I ran into an issue yesterday that brought up questions as to how the checkpoint's max outstanding, I/O target is determined. There is different behavior across the various versions of SQL Server and it would take an entire white paper to fully explain the checkpoint process. I am going to try to boil it down to the most relevant information. I previously outlined some of the checkpoint behavior in the following white papers. http://www.microsoft.com/technet/prodtechnol/sq...(truncated)...

European PASS Conference 2008

(Indexed 2008-04-10):

I'm leaving this Saturday for the Europe PASS Conference in Neuss, Germany. I have the following two sessions to present while at the conference:1) Performance Toolset WorkshopThis is day long pre-conference seminar on SQL Server Engine Performance. This session is all about the tools used by CSS to work on customer problems related to SQL Engine performance. This includes tools like Performance Dashboard Report, SQLDiag, SQLNexus, RML Utilities, and some interesting new perf tools in Windows Se...(truncated)...

Troubleshooting xp_cmdshell failures

(Indexed 2008-04-10):

This post assumes you have properly enabled the xp_cmdshell feature using the Surface Area Configuration tool and you have used Management Studio | Server Properties | Security to establish a proxy for xp_cmdshell execution. Sample command: master..xp_cmdshell 'whoami' SQL Authentication: TestLogin (Public in pubs) When xp_cmdshell is executed it returns initialization errors with error message 15121. Msg 15121, Level 16, State 200, Procedure xp_cmdshell, ...(truncated)...

How It Works: File Streams Requires Integrated Security (Windows Authentication)

(Indexed 2008-04-10):

SQL Server authentication versus Windows Authentication seems to cause File Stream users confusion. I thought this e-mail chain would help. From: Robert Dorr Subject: RE: OpenSqlFilestream problem It is really not the SQL Server Service account that matters here. SQL Server has to have access to the files in order to handle the file stream file groups. The problem that external access to the files occur throug...(truncated)...

How It Works: SQL Server Page Allocations

(Indexed 2008-04-08):

When I drop an object why does SQL Server not immediately re-use those extents for a new object? This is because the storage engine is optimized for speed to continue allocating new extents from available free space in the file rather than always going back in to routines to identify and reclaim previously dropped extents. The storage engine maintains a pointer in the FCB for each file to point to the next available (uniform or mixed extent). It continues to move...(truncated)...

SQLIOSim is "NOT" an I/O Performance Tuning Tool

(Indexed 2008-04-05):

This blog post is to again inform you "NOT" to consider SQLIOSim as an I/O performance evaluation tool! Every other month or so I get a rash of questions about using SQLIOSim to validate I/O performance and I have to repeat the same information. As I have stated in other posts and documents SQLIOSim and SQLIOStress were designed to test the I/O stability not performance characteristics. SQLIOSim specifically includes random patterns as various stages of testing that will...(truncated)...

Microsoft to Increase Use of SQL Server 2005 Best Practices Analyzer (SQLPBA) - So Should You

(Indexed 2008-04-05):

I don't want you to be surprised when you see more use of SQLBPA. It was 18 months ago when the development team approached the support team asking us to help identify issues for SQLBPA. Several of us, including myself helped identify and rank a list of rules for SQLBPA that target the repeated, most difficult and critical cases. SQL Server 2005 Best Practices Analyzer http://www.microsoft.com/downloads/details.aspx?FamilyID=da0...(truncated)... T...(truncated)...

SQL Server 2005 Memory Limits and Related Questions

(Indexed 2008-04-05):

I had a very nice conversation this week with one of the blog readers. The question related to adding more memory to their server. It lead to an exchange of various links that I think you might find handy. We discussed the operating system limitations and the SQL Server limitations. Each of these are outlined in different locations. Operating System Limits The operating system establishes installed memory limitations by SKU. SQL Server i...(truncated)...

How It Works: Non-Yielding Resource Monitor

(Indexed 2008-03-28):

I have addressed similar conditions error in a white paper: www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx 2008-01-20 19:01:26.11 Server ***Stack Dump being sent to K:MSSQL.3MSSQLLOGSQLDump0001.txt 2008-01-20 19:01:26.11 Server * 2008-01-20 19:01:26.11 Server * BEGIN STACK DUMP: 2008-01-20 19:01:26.11 Server * 01/20/08 19:01:26 spid 0 20...(truncated)...

How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000

(Indexed 2008-03-28):

SQL Server 2005 adds additional shrink logic to compress TEXT/IMAGE data, referred to as LOBs. The dbcc shrink* code uses the same underlying methods that ALTER INDEX .... WITH (LOB_COMPACTION ON) uses to compact the LOB space in the database files. There are various stages of dbcc shrink* activity. These stages are indicated in the sys.dm_exec_requests command column along with the progress indications. ...(truncated)...

How It Works: SQL Server 2008 Backup Compression, Database Compression and Total Data Encryption

(Indexed 2008-03-24):

I have seen a fair series of questions around the compression and encryption features of SQL Server 2008. The main question is can I or should I used Total Data Encryption (TDE) and backup compression? All these features work with each other but this is not always the recommended configuration. Without going into complete details, already available in books online, the following table outlines the basics. Database Compression Allows data in the database ...(truncated)...

How It Works: SQL Server 2005 SP2 Security Ring Buffer - RING_BUFFER_SECURITY_ERROR

(Indexed 2008-03-24):

SQL Server 2005 SP2 added new ring buffer entries (sys.dm_os_ring_buffers) for various security errors. The reason the ring buffer entries are added was to provide the DBA with more details as to why a client is receiving a failed login or other such error. The ring buffer entries are protected because they require view system state privileges to enumerate. The information contained in the ring buffer can be used to help understand the source of a failure. &#...(truncated)...

How It Works: DBCC ShrinkDatabase / ShrinkFile (Tabular)

(Indexed 2008-03-24):

I ran across an undocumented option that was very helpful in resolving an issue last week. WARNING: As always any undocumented syntax can change and may not be supported. This should be used under the guidance of Microsoft SQL Server Support. The customer was running a dbcc shrinkdatabase and getting the following error for several files. However, issuing a dbcc shrinkfile with a target value completes successfully. DBCC SHRINKDATABASE: File ID 1 of datab...(truncated)...

How It Works: Shapshot Database (Replica) Dirty Page Copy Behavior (NewPage)

(Indexed 2008-03-24):

I ran into an issue last week that I found interesting and thought you might find it interesting as well. The primary database had a replica (snapshot database) established and the issue reported was that the replica was acquiring additional space when only new data was added to the primary database. The understanding was that only data that existed at the time of the snapshot would need to be copied to the replica if changed (dirtied) on the primary database. The design of the replica i...(truncated)...

How It Works: SQL Server 2005 I/O Affinity and NUMA Don't Always Mix

(Indexed 2008-03-18):

Recently I have fielded several questions related to I/O affinity. Allow me to outline the behavior and clear up misconceptions. When I/O affinity is enabled SQL Server creates a hidden scheduler for the Log Writer process as well as a special lazy writer thread for each of the bits set in the I/O affinity mask. The only job of the special lazy writer(s) is to watch the read and write request lists, issue and complete the I/O requests. These special lazy writers are not respon...(truncated)...

How It Works: SQLIOSim - Checksums

(Indexed 2008-03-05):

SQLIOSim, like its predecessor SQLIOStress, is designed to read pages it has written and validate the data. SQLIOSim does this using a checksum algorithm. When SQLIOSim starts up it creates a set of buffers and used the Cryto APIs to generate random data on them. It then calculates the checksum for each of the buffers. These become statically stored. When a page is DIRTIED by SQLIOSim a random seed is calculated. Using this random seed value MOD (%) wi...(truncated)...

SQL Server Working Set Trim Problems? - Consider...

(Indexed 2008-03-03):

Microsoft support continues to receive a steady flow of cases indicating poor, SQL Server performance symptoms. When the issue is narrowed down we are finding that the working set of SQL Server and many of the processes on the computer have been significantly trimmed. What we have found is a series of 3rd party and a few Microsoft drivers that are not playing well with the operating system. Many of these are making memory allocations that are large or contiguous. W...(truncated)...

How It Works: Debugging SQL Server Stalled or Stuck I/O Problems - Root Cause

(Indexed 2008-03-03):

Previously I have covered stuck and stalled I/O issues in other posts and articles. However, these only tell you that there is a problem outside the SQL Server engine. This post attempts to extend your root cause debugging capabilities beyond the SQL Server process. http://blogs.msdn.com/psssql/archive/2006/11/27/what-do-i-ne...(truncated)... http://www.microsoft.com/technet/prodtechnol/sql/2005/diagan...(truncated)... NOTE: Consult with your Windows Administrator be...(truncated)...

How It Works: Linked Servers and Collation Compatibility

(Indexed 2008-02-14):

Customer Question: "Why I am getting different results when is change collation compatibility from true to false?" This actually turned out to be an interesting issue because no error was raised but the results are different. The query looked like the following: select * from REMOTESERVER...tblTest as t where t.strID = (select TOP 1 strID from myLocalTable) INCORRECT RESULTS: When collation compatibility was set to TRUE CORRECT RESULTS...(truncated)...

How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)

(Indexed 2008-02-13):

The question invoking the discussion was why did a query elect to use 100+ workers, approximately half the configured worker threads? Before erasing this topic from my whiteboard let me document the highlights. Often overlooked is the degree of parallelism decision is applied to each parallel operator of the plan. For this example: Assume that the degree of parallelism elected for the query is 2. As a general rule assume that the input and output side of the operator...(truncated)...

How It Works: SQL Server 2005 Connection and Task Assignments

(Indexed 2008-02-12):

I have talked about how connections and tasks get assigned as PASS and during many other mentoring opportunities. I just finished working on an issue that forced me to dig deeper into the subject. The customer reported that on a NUMA machine the connections are always getting assign to the same node. They had determined this by looking at the CPU usage information and the node_affinity assignments. select endpoint_id, node_affinity, * from sys.dm_exec_connections ...(truncated)...

How It Works: SQL Server 2005 Database Snapshots (Replica)

(Indexed 2008-02-07):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. I encountered an interesting issue today that I would like to share. The problem ended up being that the a database snapshot was established for reporting purposes and all the sudden the primary database started encountering performance problems. In this case it got bad enough that the server was reporting I/O...(truncated)...

How It Works: How does SQL Server Backup and Restore select transfer sizes

(Indexed 2008-02-06):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. A customer asked: "Why does backup to disk choose a transfer size of 1MB but restore 64K unless I specify BUFFERCOUNT and MAXTRANSFERSIZE?" WARNING: This 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 f...(truncated)...

High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT

(Indexed 2008-02-05):

If you have a SQL CLR application, you may notice that waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT from sys.dm_os_wait_stats are very high.This is normal and can be safely ignored because these waits reflect internal CLR runtime waits under normal conditions. Since SQL as a CLR host implements synchronization mechanism, it is able to track these kinds of waits and exposes them via DMV. The only exception when you need to pay attention to these two wait types is that you actually use unsafe as...(truncated)...

How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

(Indexed 2008-01-28):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. The virtual device interface (VDI) backups allow 3rd party back-up solutions to integrate with the SQL Server. This means you don't have to use the built in SQL Server output destinations. You can use solutions that place the backup on alternate storage, snapshots, use compression and other alternatives. A co...(truncated)...

How It Works: SQL Server 2005 NUMA Basics

(Indexed 2008-01-24):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. I have received several questions on the SQL Server NUMA implementation this week. Much of this was covered in my 2006 PASS presentation in Seattle but allow me to summarize. This is not a complete document by any means but it should be helpful. Hard NUMA Hard NUMA is the NUMA configuration indicated to the operating sy...(truncated)...

How It Works: Conversion of a Varchar, RPC Parameter to Text from a Trace (.TRC) Capture

(Indexed 2008-01-24):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. This was an interesting issue I ran into this week. The reported problem was that the data shown as TEXT for an RPC event did not match the data submitted by the client. SQL Server 2005 introduced an optimization for capturing RPC events. During a SQL Server 2005 capture you can exclude the TEXT ...(truncated)...

How It Works: What is Restore/Backup Doing?

(Indexed 2008-01-23):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. A customer asked: "Why does it take me 7 hours to backup my database but 21 hours to restore?" The answer could be many things. For example, if you have a 1TB database with only 100GB of data the backup would only have to take 100GB but the restore has to build the 1TB database could take longer. Another scenar...(truncated)...

How It Works: File Stream the Before and After Image of a File

(Indexed 2008-01-15):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. The question that spawned this entry was: "I am performing updates of a specific row and column and after 5 updates I see 5 files for the same row and column stored in the file stream container folder - why?" The SQL Server 2008 file stream feature allows VARBINARY(MAX) data to be stored as individual files on disk. The ...(truncated)...

SQLIOSim - Error Request Could Not Be Performed - Unable to get disk cache info

(Indexed 2008-01-14):

Both SQLIOSim and SQLIOStress use DeviceIOControl to determine information about the target. On disk caches are typically not battery backed and when enabled can lead to data loss during a power outage. The utilities attempt to warn the user when on disk caching is reported as enabled. Not all devices support interrogation of the disk cache setting. SQLIOSim logs an error condition when unable to determine the on disk cache setting instead of a warning. ...(truncated)...

RML Utilities Install Fails with MSI (Windows Installer) Error #2755

(Indexed 2008-01-11):

It is possible to encounter error 2755 when installing the RML Utilities package. The most common cause is an encrypted file. This is generally from storing the download in an encrypted folder. Reproduction Steps Store the RMLSetup*.msi on the local computer. Right mouse on the .MSI file and select properties. Use the advanced options to encrypt the contents of the file. When running setup it fails with the following dialogs during the file copy portion of the inst...(truncated)...

How It Works: SQL Server Engine Error Messages

(Indexed 2008-01-10):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. The majority of SQL Server errors are handled by a central routine named ex_raise. The routine takes a MAJOR and a MINOR identifier along with the severity, state and variable error message arguments. The error number becomes (MAJOR * 100) + MINOR. So for a deadlock error is 12, 5 or 12 * 100 + 5 = 1205....(truncated)...

How It Works: System Sessions

(Indexed 2008-01-10):

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog. Looking at a SQL Server error log it is formatted with the date, time and session identifier. Many of the identifiers contain the s following the spid value. 2008-01-08 20:03:36.12 spid5s The s indicates that the session is a system session. Prior to SQL Server 2005 all system sessions were limite...(truncated)...

SQL Server 2008 - Resource Governor Questions

(Indexed 2008-01-10):

A couple of common questions have surfaced related to the SQL Server 2008 Resource Governor feature. The SQL Server 2008 BOL has a lot of good information so start don't forget to review that as well. Metadata and Runtime Data A set of DMVs exist for resource governor. The 'dm_' indicates the current 'in-use' (running values and statistics). These DMVs are helpful to view the various pool and group activities and supplement the performance counters well. sys.d...(truncated)...

RML Utilities for Microsoft SQL Server Released

(Indexed 2007-12-18):

[Image] The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005. The utilities are collectively called the RML Utilities for SQL Server. With the RML Utilities you can answer questions such as the following: Which application, database or lo...(truncated)...

RML (READTRACE/OSTRESS) Utilities for SQL Server 2005

(Indexed 2007-11-21):

As I promised at the PASS 2007 Summit in Denver, I'm writing a post here about the popular RML Utilities better known and READTRACE and OSTRESS. Our goal was to have this fully ready and posted on the web for public download by Thanksgiving. That day has arrived but we are not quite ready to post the final bits.The authors of these tools, my colleagues at Microsoft Keith Elmore and Robert Dorr, assure me that this is very close for final release. In fact, it is just a few final touches that is r...(truncated)...

Do I have to assign the Lock Pages in Memory privilege for Local System?

(Indexed 2007-10-18):

Some who attended one of my talks at the recent PASS conference asked me the following question "Do I need to use the Group Policy Editor to assign the Lock Pages in Memory privilege if my SQL Server Service is running under the Local System Account?". The answer to this question is no and here is why.If you want to determine whether SQL Server 2005 is actually taking advantage of the Lock Pages in Memory privilege, look for the following entry in your SQL ERRORLOG when it is starting up:Using l...(truncated)...

Database compatibility and new features

(Indexed 2007-10-16):

Have you attempted to execute queries using some of the new keywords and constructs only to see error messages like the following?Msg 321, Level 15, State 1, Line 5"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90. Well, the error message is correct. You cannot make use of several new OPERATORS, KEYWORDS and other functionality when the database, in whose context you are exec...(truncated)...

Follow-up to PASS 2007 Summit

(Indexed 2007-09-27):

Our entire PSS team had a great time presenting and meeting many customers at last week's summit. Thank you for your interest in SQL Server and bringing so many "interesting" questions to the Service Center. A few follow-up notes for those of you who attended the summit:1) If you attended one of our pre-conference seminar talks, we will be providing PASS the demo files and materials next week. If you registered for one of these seminars PASS tells me they will send you a URL to download all of t...(truncated)...

Microsoft PSS is back for the PASS 2007 Summit!

(Indexed 2007-09-13):

Since 2003, Microsoft PSS engineers have been speaking and meeting customers at the PASS Summit. 2007 will be no different. In fact, this year ten of our top PSS engineers are coming into the conference to speak, host labs, and meet customers. I thought I would briefly outline how we will be involved in the summit this year:Pre-Conference Seminars:Monday, September 17, 2007 - The Performance Toolset WorkshopJoseph Pilov and myself will be presenting tools and techniques used by the Microsoft PSS...(truncated)...

SQL Server 2000 with high LOCK_HASH spinlock contention

(Indexed 2007-08-28):

I want to make you aware of an update (QFE) to address LOCK_HASH contention during logon and sp_reset_connection requests. The details of the fix are scheduled to be published under knowledge base article 939317. Customers have generally reported increased than expected CPU and unexpectedly high values for the LOCK_HASH row in dbcc sqlperf(spinstats). If the server has such symptoms you might consider contacting Microsoft for the SQL Server 2000 QFE.[Image]

SQL Server 2005 - RDTSC Truths and Myths Discussed

(Indexed 2007-08-19):

I posted some updates to my blog entry about the RDTSC and drift warnings in May. See the following link http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2...(truncated)... Because the CPU drift issue has continued to generate questions let me try to answer these directly. Myth: Performance Problems The statement I have seen frequently is that the drift warning equates to performance problems. This is not the case and extensive testing by the SQLOS team has shown that the even u...(truncated)...

NUMA Connection Affinity and Parallel Queries

(Indexed 2007-06-28):

The SQL Books Online states that using NUMA port affinity helps co-locate the resources used by the query (CPU, Memory, .). This is true for serial plan execution but not completely the case for parallel query execution. You might assume that you can use the NUMA port affinity and all the parallel work is completed on the node(s) the connection is affinitized to but this is not the case. NUMA port affinity is not taken into account when the parallel query decision is made. The parallel qu...(truncated)...

How to configure Database mirroring for use with ODBC applications?

(Indexed 2007-06-22):

If you have an ODBC application that is connecting to a mirrored database and ifThe application uses a connection string without a database name ( for e.g: dsn=ssistest;uid=ssistest;pwd=*********)The DSN is configured to use SQL authentication with a non-blank password.The default database in the DSN configuration is changed to the mirrored database. You will see the following error message when the database is failed over from primary to secondaryERROR [28000] [Microsoft][SQL Native Client][SQ...(truncated)...

The SQL Server Working Set Message

(Indexed 2007-05-31):

I spent several days last week investigating reported case trends and I will attempt to summarize the findings for you.The following message was added to SQL Server 2005 SP2 to indicate that the working set (RAM resident portion of SQL Server) was paged out. This is a common indication of performance problems due to the paging.A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 24484, committed...(truncated)...

SQL Server 2005 Service Pack 2 (SP2) Re-release and post fixes explained

(Indexed 2007-04-06):

The dust has now settled after the release of SP2 for SQL Server 2005 so I thought I would take some time to explain a few details about the re-release of Service Pack 2 and the fixes that have been posted by Microsoft since this re-release. There is much to digest in this post so at the end of the post I've created a summary to help guide you on the next steps to take:SP2 Re-release and GDR1We re-released SQL Server 2005 SP2 on March 5th after discovering an issue with maintenance plans and ass...(truncated)...

SQL Server 2005 Performance Dashboard Reports

(Indexed 2007-03-30):

The SQL Server 2005 Performance Dashboard reports were recently released as a free download for customers. The performance dashboard was developed as a joint effort between the support and development teams with a goal of reducing the amount of time spent discovering the source of a performance problem and allowing efforts to be focused on resolving the problem. The dashboard reports rely exclusively on the SQL Server 2005 dynamic management views, utilizing data that is already captured and a...(truncated)...

SQL Server 2005 SP2 Trace Event Change Connection Based Events

(Indexed 2007-03-29):

Starting with SQL Server 2005 SP2 the connection based events are also produced for sp_reset_connection activities, indicating the connection was reset. The pattern will look like the following.RPC:Starting sp_reset_connectionDisconnect Subclass = 2RPC:Completed sp_reset_connectionConnect Subclass = 2The subclass = 2 indicates that the connection based events are not physical connection activities but the logical reset opera...(truncated)...

SQL Server 2005 Service Pack 2 has released! Linked Server GUI is back!

(Indexed 2007-02-21):

SQL Server 2005 Service Pack 2 (SP2) released on Monday to the web. If you want to start downloading it while reading the rest of the post, go right to http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo...(truncated)... the next several weeks, several of us in PSS will be posting blogs specifically on changes or features for SP2 focusing on troubleshooting and supportability.I'll kick start this series of posts by telling you that we have brought back GUI features for linked server que...(truncated)...

Daylight Savings Time (DST) Changes for 2007 and their impact on SQL Server

(Indexed 2007-02-21):

As many of you know, Daylight Savings Time (DST) in the United States is changing this year to start on March 11th, 2007 (3 weeks earlier than normal) and end on November 4th, 2007 (1 week later than normal). I thought I would write a quick post on the impact of these changes on SQL Server.The impact can be summarized in 3 key points:Notification Services is the ONLY SQL product that has a DST 2007 bug and requires a fix to be applied. You can find this update at the following KB article http://...(truncated)...

SQL Server Urban Legends Discussed

(Indexed 2007-02-21):

I have been answering questions on two of the most misunderstood SQL Server Urban Legends frequently again so I decided to make a post about them. SQL Server Uses One Thread Per Data File A Disk Queue Length greater than 2 indicates an I/O bottleneckSQL Server Uses One Thread Per Data FileThe Legend grew from the following SQL Server behavior - When SQL Server is creating a database file (.mdf, .ndf, .ldf) it writes zeros to all bytes in the file and stamps the appropriate allo...(truncated)...

The Importance of the Trace Event Sequence Column and SQL Server 2005 SP2 Changes

(Indexed 2007-02-21):

Many of us are used to looking at a SQL Trace and making the assumption that the order of display is what occurred during the capture. SQL Server 2005 introduced a new column named Event Sequence that you should capture for any trace events. The Event Sequence is the order the trace event was internally created in the server which can be different from the physical file order due to various timing and design issues. The Event Sequence gives you a more accurate representation of the event or...(truncated)...

SQL Server 2005 Performance Statistics Script

(Indexed 2007-02-21):

Many of you have used the SQL Server 2000 blocker script (see KB 271509) to help troubleshoot performance issues in SQL 2000. If you haven't used it, you probably use a script of your own that takes a similar approach, polling sysprocesses and virtual tables to detect blocking and other resource contention issues. In PSS weve started using a new performance troubleshooting script for SQL 2005; we call this one our "Perf Stats Script", since it's useful for troubleshooting lots of issues in add...(truncated)...

SQL 2005 Setup Failures

(Indexed 2007-02-01):

This week I was reviewing Watson setup failure rates and comparing them to cases opened in the same areas with SQL Server support. Dec 2006 was the highest volume level for SQL Server 2005 RTM and SP1 cases to date.The values were not out of line, in fact encouraging to a reasonable degree. Many of you took advantage of your holiday, production lockdowns to check out SQL Server 2005.I want to take this opportunity to encourage you to use the self help feature in SQL Server 2005 setup. When s...(truncated)...

SQLServerCentral Interview with Bob Dorr from Microsoft PSS

(Indexed 2007-01-31):

I want the community to get to know more some of the people that work in Microsoft PSS with SQL Server (one of the reasons to start this blog). Hopefully you have had a chance to meet some of us at PASS.Here is another opportunity. At the recent PASS conference, my colleague Bob Dorr was interviewed by SQL Server Central Magazine. Check out the interview and find out more about Bob's passiong for fishing at http://www.sqlservercentral.com/columnists/sjones/2830.asp.Bob Ward, Microsoft[Image]

Processing appears to stall or become sluggish on multi-processor machines running Analysis Services 2005

(Indexed 2007-01-16):

Over the past several months PSS has received several reports of processing for cubes in SQL Server 2005 Analysis Services hanging or becoming unbearably slow. Most of the reports have involved high end 64-bit servers with 4 or more processors and 8GB or more RAM. In many instances the processing can be jump started again by connecting and issuing a simple MDX query.This problem turns out to be due to a change in the way Analysis Services configuration is handled in SQL Server 2005 vs SQL Serv...(truncated)...

SQL Server Reporter

Search Posts:

Search Jobs:

273 posts from: PSS SQL Server Engineers

(Indexed 2010-09-01):

(Indexed 2010-08-24):

(Indexed 2010-08-24):

(Indexed 2010-08-18):

(Indexed 2010-08-16):

(Indexed 2010-08-13):

(Indexed 2010-08-04):

(Indexed 2010-08-03):

(Indexed 2010-07-29):

(Indexed 2010-07-29):

(Indexed 2010-07-21):

(Indexed 2010-07-09):

(Indexed 2010-07-09):

(Indexed 2010-07-08):

(Indexed 2010-07-04):

(Indexed 2010-06-23):

(Indexed 2010-06-21):

(Indexed 2010-06-21):

(Indexed 2010-06-12):

(Indexed 2010-06-08):

(Indexed 2010-06-08):

(Indexed 2010-06-08):

(Indexed 2010-05-27):

(Indexed 2010-05-27):

(Indexed 2010-05-27):

(Indexed 2010-05-18):

(Indexed 2010-05-11):

(Indexed 2010-05-06):

(Indexed 2010-05-05):

(Indexed 2010-04-23):

(Indexed 2010-04-21):

(Indexed 2010-04-20):

(Indexed 2010-04-13):

(Indexed 2010-04-11):

(Indexed 2010-04-02):

(Indexed 2010-04-01):

(Indexed 2010-03-24):

(Indexed 2010-03-23):

(Indexed 2010-03-16):

(Indexed 2010-03-09):

(Indexed 2010-02-23):

(Indexed 2010-02-22):

(Indexed 2010-02-19):

(Indexed 2010-02-17):

(Indexed 2010-02-17):

(Indexed 2010-01-29):

(Indexed 2010-01-21):

(Indexed 2010-01-11):

(Indexed 2010-01-08):

(Indexed 2010-01-08):

(Indexed 2009-12-30):

(Indexed 2009-12-28):

(Indexed 2009-12-17):

(Indexed 2009-12-15):

(Indexed 2009-12-10):

(Indexed 2009-12-09):

(Indexed 2009-12-03):

(Indexed 2009-12-02):

(Indexed 2009-12-01):

(Indexed 2009-11-24):

(Indexed 2009-11-20):

(Indexed 2009-11-20):

(Indexed 2009-11-17):

(Indexed 2009-11-13):

(Indexed 2009-11-03):

(Indexed 2009-10-26):

(Indexed 2009-10-23):

(Indexed 2009-10-15):

(Indexed 2009-10-07):

(Indexed 2009-09-29):

(Indexed 2009-09-23):

(Indexed 2009-09-22):

(Indexed 2009-09-17):