CSS SQL Server Engineers

Analysis Services Thread Pool Changes in SQL Server 2012

psssql — Tue, 31 Jan 2012 21:40:00 GMT

As part of the SQL Server 2012 release several changes were made to Analysis Services that should alleviate some issues previously seen on large NUMA node machines.

Separation of IO and Processing Jobs

To better understand how the changes that were made work, it is helpful to have some background information about what types of issues were seen in earlier releases.

During testing, it was found that a 2 NUMA node 24 core server was handling roughly 100 queries per second during stress testing. The same tests were then run on a 4 NUMA node server with 32 cores and the queries answered per second actually decreased. Investigation indicated the cause as cross NUMA node access to the system file cache. For example if the first job that read a file was scheduled on a CPU assigned to NUMA node 1 and a subsequent job, which needed to read the same pages from the file, was scheduled on a CPU assigned to NUMA node 3, a significant performance penalty was seen reading the cached file page. Deeper investigation determined that the bottleneck appeared to be in accessing the standby list of file cache pages. The performance impact appeared to become more pronounced the higher the number of CPUs and NUMA nodes on the machine. For machines with less than 4 nodes the impact was not found to be significant. However, on machines with 4 or more nodes the impact was such that performance could begin to regress.

One possible work around is to open files in Random mode since this changes the file cache access behavior. To accommodate this SSAS 2012 now allows Random file access to be configured through the msmdsrv.ini file by setting the RandomFileAccessMode property to a value of 1. This server property does not require a service restart to take effect, but a restart is recommended. If the server is not restarted then Analysis Services will not release open files or change the way it accesses open files, but the setting will take effect for newly opened or created files.

While changing the file access mode may provide some relief, eliminating cross NUMA node file access whenever possible is a better long term solution. Towards this goal, SQL Server 2012 Analysis Services now has separate Process and IOProcess thread pools which are NUMA node aware. The new IOProcess thread pool will handle read jobs while the Process thread pool will continue to handle ROLAP and processing related jobs, including writing new files. On machines with 4 or more NUMA nodes, the IOProcess thread pool is not a single thread pool but instead a collection of thread pools with each NUMA node having its own pool of IOProcess threads. Assigning each NUMA node its own IOProcess pool doesn’t by itself help with cross NUMA node file access unless file IO operations are consistently assigned to the same IOProcess thead pool, so an algorithm for assigning partition file read operations to specific IO thread pools was also added. At RTM, the algorithm spreads partition reads across IO thread pools based on the ordinal position of the partition in the partitions collection for a measure group. The algorithm is subject to change without notice in future builds so design decisions should not be made based on this behavior.

An attentive reader will notice that the discussion has highlighted partition reads to this point, what about dimension operations? Dimension read jobs are always assigned to the IOProcess thread pool for NUMA node 0. While one could argue that this scheme could result in NUMA node 0 being assigned a larger percentage of the work, it is expected that most dimension operations will be operating on cached data in memory and won’t have a noticeable impact.

Because IOProcess threads are expected to perform short duration read operations on files and as such do not register themselves as cancellable objects. This means that even if a query which requested an I/O operation was cancelled, the in-process I/O jobs could continue to run for a short period of time after the query was cancelled, however new I/O jobs should not be created for the canceled query.

As with other SSAS features, the default behavior of the per NUMA node IOProcess thread pools is intended to cover the most common scenarios. However, in some situations it may make sense to override the 4 NUMA node threshold for the NUMA node affinitized IOProcess thread pools. To allow administrators to revert back to a single IOProcess thread pool or force per NUMA node IOProcess thread pools for machines with less than 4 NUMA nodes a new entry has been added to the Analysis Services configuration file (msmdsrv.ini). The PerNumaNode setting under ThreadPool\IOProcess has a default value of -1 which tells the server to use the automatic 4 NUMA node threshold. Changing this value to 0 disables the per NUMA node thread pool behavior, while setting it to 1 will turn on this behavior (even if there are less than 4 nodes).

The splitting of Process and I/O jobs into separate thread pools and the assignment of I/O jobs to consistent NUMA nodes should alleviate some of the performance impacts of cross NUMA node operations, significantly increasing the performance of SSAS workloads on higher end servers.

Greater than 64 CPU support and Thread Affinitization

In adition to giving IO operations their own thread pools, Analysis Services now supports more than 64 CPUs, something the relational engine has had for a while.

In order to add support for more than 64 CPUs, Windows uses the concept of processor groups. A processor group in Windows can contain a maximum of 64 CPUs and systems with more than 64 CPUs will contain multiple processor groups. For more details on processor groups and support for more than 64 CPUs read the following:
http://msdn.microsoft.com/en-us/library/dd405503%28VS.85%29.aspx
http://blogs.msdn.com/b/saponsqlserver/archive/2010/09/28/windows-2008-r2-groups-processors-sockets-cores-threads-numa-nodes-what-is-all-this.aspx

To support multiple processor groups and thus more than 64 CPUs, SSAS 2012 was updated to set the process affinity mask for the msmdsrv.exe process to span multiple processor groups. Along with this capability a new configuration property named GroupAffinity was added for each thread pool in the server. This property allows an SSAS administrator to have fine grain control over which CPUs on a machine are used for each thread pool. The GroupAffinity setting is a bitmask that is used to determine which CPUs in a processor group can be used for the thread pool in which the GroupAffinity mask is defined. For example if the following entry:
<GroupAffinity>0xFFFF,0xFFFF</GroupAffinity>
were to appear under <ThreadPool> <Process> in the msmdsrv.ini file, it would affinitize threads to 16 logical processors in the first two processor groups on the server. Where the following entry:
<GroupAffinity>0x00F0,0xFFFFFFFF</GroupAffinity>
would affinitize threads to the CPUs 4-7 in the first processor group, and the first 32 CPUs in the second processor group. The GroupAffinity property can have as many comma separated hex values as there are defined CPU groups on a server. If the mask contains less bits than the number of CPUs for the processor group then it is assumed that non-specified bits are zeros. If no GroupAffinity value is specified for a thread pool (default) then that thread pool is allowed to spread work across processor groups and CPUs on the box.

For diagnostic purposes the msmdsrv.log file now contains entries at service start that reflect the size of each of the five thread pools (Query, ParsingShort, ParsingLong, Processing, and IOProcessing) and their settings, including affinity.

Example:
(12/21/2011 4:16:04 PM) Message: The Query thread pool now has 1 minimum threads, 10 maximum threads, and a concurrency of 4. Its thread pool affinity mask is 0x0000000000000003. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.DENALIRC0\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000A)

Note: Although VertiPaq can use more than 64 CPUs, GroupAffinitization is not currently supported for the VertiPaq thread pool, even though an entry exists in the msmdsrv.ini file.

While the GroupAffinity setting was added as part of the work to support more than 64 CPUs, this property can also be used on servers with less than 64 CPUs to control which CPUs are used for specific operations. Through use of the GroupAffinity mask administrators can push I/O, processing, query, or parsing threads to specific CPUs to obtain optimal resource usage, or better enable resource sharing across multiple processes on the same server.

Wayne Robertson, Sr. Escalation Engineer Analysis Services

Management Studio tricks you may or may not know about

Adam W. Saxton — Tue, 31 Jan 2012 17:33:52 GMT

I was sitting through SQL Server 2012 training, and Ajay Jagannathan was showing us Management Studio. Eric Burgess had worked on this topic. He started covering some neat things, that have apparently been there for a while, but I just never played around with it enough to see what all you could do. I’m sure a lot of others are not aware of these either, so I thought I would highlight some of them. These may help to make you more productive, or not - depending on how long you spend playing with these. The biggest change for Management Studio within SQL Server 2012 is that we are using the Visual Studio 2010 Shell.

These items aren’t exactly new, but they were new to me.

1. Keyboard shortcuts

There are a ton of Keyboard shortcuts that you can use within Visual Studio. The default settings are based on Visual Studio 2010. Here is a list of those shortcuts. SQL Server Management Studio Keyboard Shortcuts

You can get to these through Tools -> Options -> Environment/Keyboard

2. Block Selection

Sometimes you may only want to select and copy a column of text as opposed to the normal text selection done by holding down the Shift Key. To do Block selection, you can do SHIFT+ALT and drag your mouse to only select certain areas of your text in column fashion.

3. Status Bar

Most people should be familiar with the Status Bar at the bottom of a query window.

Under Tools -> Options -> Text Editor/Editor Tab and Status Bar, there are a bunch of options that you can play with.

One is the Status Bar Location. By default, this is set to Bottom, but you can move it to the top if you choose. This was a little weird for me though - although I’m just not use to it. It does put it more front and center.

From a color perspective, you’ll notice Group Connections and Single Server Connections. I’ll talk about Group connections below, but just be aware that you can change the coloring for when it is a group connection.

4. Cycle through Query Windows

I’ve known about ALT+TAB to cycle through programs (Windows). I’ve also known about CTRL+TAB to cycle through components within a given application. For example in Excel you could use CTRL+TAB to move between worksheets. I’ve never tried it in Management Studio, but it allows you to cycle through the Query Windows.

Also, CTRL + F6 will cycle through the actual tabs without the graphic switching display.

5. Group Connections

You can go to View and select to show Registered Servers. Within Registered Servers you can create a group of SQL Servers. This then allows you to start a query that will be run against all of the servers within the group. This is where the Group Connection Color for the status bar comes into play.

This could be really handy if you need to execute items across multiple servers. The color of the status bar is there to help you realize that that query is a group query as opposed to a single server connection.

Hopefully these are useful to you.

Adam W. Saxton | Microsoft Escalation Services
http://twitter.com/awsaxton

The case of the incorrect page numbers

Evan Basalik — Sun, 29 Jan 2012 23:46:04 GMT

As you may or may not know, SSRS 2008 R2 added the ability to automatically create page breaks on group changes. Historically, people attempted to use custom code to accomplish this and, while it worked, the pagination logic we use in SSRS 2008 R2 breaks the standard implementation of this for several reasons. You can see the typical implementation of this at http://blogs.msdn.com/b/chrishays/archive/2006/01/05/resetpagenumberongroup.aspx.

Unfortunately, in SSRS 2008 R2 the pagination engine does multiple passes in some scenarios thus breaking this logic. Also, the logic referenced above makes the assumption that the report is rendered from page 1 to page N and this assumption is not necessarily true either. We recognized that losing that ability to do the pagination at a group level is a key feature so we actually rolled it completely into the product. Robert Bruckner (one of the SSRS developers) gives a really good explanation of all of the ins and outs of the feature at http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/report-design-reset-page-number-on-group.aspx.

I wish I could say that this feature has been completely without pain, but that is not true. If any of you have been working with SSRS for any length of time, you know that there are all kinds of quirks to the pagination process. Given the correct information, it is always fairly easy to figure out why the pagination engine resulted in a certain pagination, but understanding all the pertinent details is….a bit difficult. In a lot of ways, I view pagination sort of like the query optimizer in the SQL Server engine. It seems like total black magic if you don’t know the rules it follows, but once you understand the rules it generally makes good sense. However, again like the query optimizer, the pagination logic is very complex and is something that we are constantly refining. As you can imagine, people regularly find edge cases where the pagination either doesn’t function as they want or flat out makes a mistake. In addition, layering group level page breaks on top of normal pagination makes the scenario even more complicated. Thus, I have to make the admission that we have had several fixes released in this specific area. Sigh…

The good news is that I have also generally found that a good chunk of my page numbering problems go away by using the latest CU. The bad news is that a simple fix like upgrading to the most recent CU didn’t fix my most recent problem.

So, here’s the situation. My customer wanted to reset the page number whenever the group changed. No big deal in theory. As documented both in the MSDN documentation and Robert’s blog, all they had to do was to add a couple of properties to the target group:

No big deal, right? They completely expected to see the pagination look something like this:

and , etc.

This is what my customer saw instead:

What the heck????!?!!?!?!

Needless to say, this is when they decided to open a case with Microsoft Support. The problem got even weirder when I discovered that under no circumstances could I duplicate the behavior!!! I could reproduce a whole host of problems (2 of 1, 9 of 6), but by applying some workarounds and the latest CU’s I could always get back to normal page numbering resets. At this point, I was completely stumped. I even engaged the SSRS Product Group to ask if there was some sort of race condition that could lead to this behavior. Unfortunately, the answer I got back was that there was no identifiable race condition scenario where 1 of 337 could happen.

Then, as always seems to be the case, I put the problem aside for a couple of days to let it stew. And then, while driving into work the next Sunday, it hit me. Target server version!

You see, it had occurred to me that one of the good things about BIDS 2008 R2 is that it allows you to publish against both a 2008 and a 2008 R2 instance of SSRS. *However*, the thing I had never checked before was how we dealt with a 2008 R2 feature when deploying against 2008. It turns out we strip the feature out during the publish process. When I change my project properties to target a 2008 instance even though my instance is actually 2008 R2, this is what I saw:

Building ‘FailingScenario.rdl' for SQL Server 2008 Reporting Services.

A PageBreak property was removed from the data region, rectangle, or group ‘GroupOnWhichToIterate’. SQL Server 2008 Reporting Services does not support the PageBreak property ResetPageNumber.

The PageName property specified on the data region, rectangle, or group ‘GroupOnWhichToIterate’was removed from the report. SQL Server 2008 Reporting Services does not support the PageName property.

Build complete -- 0 errors, 2 warnings

This led me to ask my customer what version of SSRS their instance of BIDS was targeting as guess what? It was SSRS 2008!

The good news is that getting the desired group level page breaks was as easy as setting the target version to Reporting Services 2008 R2 and deploying the report.

The official release of System Center Advisor…

psssql — Fri, 27 Jan 2012 05:49:53 GMT

If you have followed this blog, you have probably seen a series of posts documenting the life of a project I’ve been working on, Atlanta, to a product called System Center Advisor. Today marks the official release of that product. This has been a particularly rewarding journey for me to see an idea about giving our knowledge in CSS to customers turn into a full proactive assessment service powered by System Center and the cloud.

In my past blog posts, I’ve shown you examples of the knowledge this product brings in the form of alerts and configuration history. With the official release comes new features such as a new dashboard about the assessment of your servers, a revised and simplified setup program, voting buttons to tell us if the alerts are useful, and the ability to provision other users to view or administer your console.

While I love to see new features like these that make this product compelling, the power of this product is in the knowledge. So while System Center Advisor has been ramping up towards this release while being a release candidate, our CSS teams have been adding knowledge in the form of alerts each month based on actual customer experiences.

Here is an example. Several months ago, one of our engineers in CSS pointed out to me that we have seen a trend where our customers would contact us and report the following error in Management Studio when trying to expand the Databases Node:

After some investigation, we found out the customer had disabled the ‘guest’ user in the msdb database. As it turns out, Management Studio requires the login connecting to have “CONNECT” access to msdb. If the login is not mapped directly to a user in msdb (usually not the case), it needs to use guest. If guest is disabled, then any access to msdb (Ex. use msdb) would result in this error.

We looked into this further and found out this was not a one time occurrence. Several customers had reported this problem to us and from other sources (Microsoft Connect, …). Why would so many people hit this? We asked one customer and they said they were simply following guidelines established in our documentation as outlined at:

http://msdn.microsoft.com/en-us/library/ff848752.aspx

In this part of the docs, the tip for the guest account says:

While the server itself doesn’t allow you to disable guest in master or tempdb (You get the error Msg 15182 Cannot disable access to the guest user in master or tempdb. if you try), for msdb we unfortunately allow this. While the server will run in this scenario, the use of tools like Management Studio will have problems. So customers were just doing what we told them.

While changing the product to prevent this is the right thing to do, what do we do in the meantime to warn customers this could be a problem? We can certainly publish articles and documentation for them to discover but why not automate the check? When I heard about this issue, I thought What a perfect rule for System Center Advisor

So as part of our monthly update to rules for SQL Server we introduced this check on your system. Should you disable guest for msdb you will get an alert which will direct you to the following technical article written by CSS talking about the problem and how to solve it:

http://support.microsoft.com/kb/2539091

This is the power of what Advisor can bring to you. We continue to update our rules each month as we discover trends about common customer problems or customer problems that may be difficult to detect or find by just searching the web.

This journey is far from over. While we to continue to create new rules for SQL Server, both the System Center Advisor team and CSS will continue to enhance this product including:

Rules for other server products (there are already several rules for the core Windows OS, Active Directory, and Hyper-V)
Provide support for SQL Server 2012 (and new rules unique to that version)
Expand features and capabilities of the System Center Advisor software and portal

System Center Advisor is available in 26 countries and comes at no charge to customers that have a Software Assurance agreement. Don’t have Software Assurance but want to try it out for free? Install it from the System Center Advisor site and choose the 60 day free trial option. Don’t know if your company has Software Assurance? Talk to the team in your company that handles volume licensing for Microsoft purchases (they typically have access to the Volume Licensing Service Center site).

If you try System Center Advisor and want to provide feedback, there is a Feedback link at the top of the Advisor console. However, I’m always personally interested to hear directly from customer experiences good and bad on how this product works for you. So feel free to comment on this post on your experiences.

Bob Ward
Microsoft

How It Works: sys.dm_tran_session_transactions

psssql — Tue, 24 Jan 2012 16:47:21 GMT

For some reason I have been looking at DMV output closely the last couple of weeks. I just blogged about the pending I/O requests and now I have a behavior to outline for dm_tran_session_transactions for todays blog.

select * from sys.dm_tran_session_transactions

The scenario I was looking at is as follows.

Server A Server B

Broker calls activation procedure

Begin Tran (local)

Linked Server Query Transaction Promoted to DTC Transaction Imported from Server A

xp_cmdshell Looped back and blocked
Separate Transaction Blocking

The process is based on SQL Service Broker.
The service broker session on Server A is 20s.
It starts a local transaction and eventually performs a linked server query.
This causes the transaction to be promoted to a DTC transaction.

At this point in time the *active transaction* DMVs on both Server A and B show the enlisted UOW of the DTC transaction. That is, except the sys.dm_tran_session_transactions on server A.

After some digging I uncovered that the dm_tran_session_transactions DMV only outputs rows for sessions (s) that are NOT system level sessions. Since the broker activity is handled on a system session the DMV will not materialize a row for Session 20s on Server A in this example. Instead you have to use the additional *active transaction* tables to track the UOW across this system.

Note: I am able to use any transaction (local or DTC) as part of SQL Service Broker activation which will not show rows in the session transactions DMV because it is considered a system session.

Bob Dorr - Principal SQL Server Escalation Engineer

How It Works: sys.dm_io_pending_io_requests

psssql — Mon, 23 Jan 2012 20:28:52 GMT

I have been working an issue where the DMV was returning io_pending_ms_ticks incorrectly. The following output is an example of ~164 hour delay. Unlikely to occur without any other side effects noted.

select * from sys.dm_io_pending_io_requests

In the process of this investigation I uncovered several details that I found helpful to share.

Full Text and File Streams
There is a small set of I/Os that could show a large io_pending_ms_ticks value but io_pending should = 1. There are administrator interfaces for both the full text and file stream features. Think if them a bit like a new TDS connection to the server. When full text or a file stream request arrives the request needs to be processed. These requests are simply waiting on the arrival of a new request from the respective features. They seldom show up on this list and the file handle will not map to any of the handles exposed in the DMF - virtual_file_stats.

io_pending is the key
The io_pending column indicates 1 if the I/O is still pending within the kernel. A value of 0 indicates the I/O only needs to be processed by the local SOS scheduler. In this case we are not getting any I/O delay warnings, performance monitor is not showing I/O issues and there are no SOS scheduler issues.

Dirty Read
After some more digging the issue is a DIRTY READ. SQL Server maintains a set of I/O request structures (request dispenser). When the I/O completes the request structure is returned to the dispenser and can be reused for another I/O. The DMV needs to materialize the list without causing scheduler issues on the system so it is designed to perform (NO LOCK) dirty reads of the I/O list. This is where your system may return you incorrect io_pending_ms_ticks value when the io_pending flag = 0.

Take the diagram below as an example. It is possible that the DMV is being materialized on one scheduler but the I/O is being completed on the I/Os owning scheduler. (Each scheduler has its own I/O list).

The DMV query can get access to the I/O request structure but it does not hold any locks on the structure. If it did it could lead to unwanted blocking. Take for instance that the application fetching the DMV output stalls. If a lock is held on the I/O list the lock can't be released until the entire list is properly traversed. Because the client is not fetching results it could lead to the I/O being stalled on the scheduler for a long period of time.

To avoid this the list is run in a dirty fashion. However, this means the IO request could finish I/O completion in parallel with the reading of the structure data. The SQL Server protects the DMV query but does not indicate to the DMV user that no lock data movement has occurred. Instead it is possible that the output can become skewed, as shown in previous output. The IO data structure can be re-assigned between the time the DMV query starts to read the information and the time all columns are produced, leading to unexpected output.

SQL Server 2012 (Denali) updates this behavior by adding a signature to the I/O request. This allows SQL to maintain the dirty read capability while also identifing I/O requests that fall into this category.

When you see large pending_io_ms_ticks consider the io_pending flag and additional scheduler warning information (178** messages) in the error log.

Bob Dorr - Principal SQL Server Escalation Engineer

Assigning SQL Server, SQL Agent to a Processor Group (OOM, Hang, Performance Counters Always Zero for Buffer Pool, …)

psssql — Mon, 23 Jan 2012 20:00:23 GMT

Suresh brought to my attention that we have been getting questions as to why SQL Server starts on group 1 and then group 2 and it is not predictable? Then Tejas brought up another issues and since I worked on this way back before we released SQL 2008 R2 I went back to my notes to pull up some details that I thought might be helpful.

The answer is that you need a SQL Server that is Group Aware to use more than one group worth of CPUs in Windows. However, if you have an older version or a SKU that does not support enough CPUs to span groups the default is for Windows to start the service on any group.

SQL Server 2008 R2 uses the group aware so it will use new APIs and establish proper use of the entire system.

Legacy XPROC / COM Object / Linked Server using a CPU based scheme.

Some designs of legacy components may not be group aware or safe. In the following example, if originally loaded on Group 2 the initialization would see 60 CPUs and create 60 partitions for a local memory manager which might work perfectly with proper synchronization. However, if the original initialization of the partitions occurs on Group 1 it will only create 40 partitions and access from CPUs 41 ... 60 on Group 2 may fail as they don't exist. (SQL Server does not have any of these components.)

Great Legacy Reference: http://msdn.microsoft.com/en-us/windows/hardware/gg463349.aspx

Windows allows you to configure node to group assignments

By configuring Windows to see the same number of CPUs and Nodes within each processor group the SQL Server, SQL Agent, … services can start on any processor group and they will see the same amount of resources.

Note: There is a hotfix for Windows 2008 R2 that automatically balances the processor assignments are corrects the issue for most systems: http://support.microsoft.com/kb/2510206

The following knowledge base article outlines how to assign nodes to the processor groups so you have control over the group assignments. How to manually configure K-Group assignment on multiprocessor machines: http://support.microsoft.com/kb/2506384

You can use Windows Task Manager to see and set the affinity for the process

Start with the Windows Task Manager | Process Tab.

Select a process, Right Mouse | Set Affinity -- the following dialog is presented showing you the Processor Groups (K-Group), Nodes and CPUs on the machine.

The Problem

Even after all this work there is an issue using older versions of SQL Server (SQL 2008 pre-SP3 and SQL 2008 R2 pre-SP2) which requires SQL Server to startup in the group that holds physical NUMA Node = 0 and at least 1 CPU assigned from physical node 0 to the instance, or lazywriter activity is not started property. This can lead to buffer pool sizing, performance counters not getting updated and other stall and hang like behaviors (OOM, Latch Timeouts, …) of the SQL Server process.

As a general rule is best to start them in group 0 (usually contains physical NUMA node = 0 but you must confirm this.)

You can enforce this on the system by setting the ImageFileExecutionOptions - NodeOptions value and by making sure the SQL Server affinity mask contains at least 1 CPU in physical NUMA node 0. However, this forces all instances of SQL Server to start on the same node and all instances have to share physical NUMA node = 0.

Excerpt from the Windows 2008 R2 Release Notes

"Allocation of child processes among the ideal nodes of Non-Uniform Memory Access (NUMA) nodes is not efficient, which results in performance degradation, increased latency, and cache misses, depending on the affected processes.

To correct this, edit the registry to change the inheritance of ideal NUMA nodes so that generated child processes are assigned the same IdealNode as their parent process. This setting is not system wide, but per-process.

To change the NUMA inheritance

Open Registry Editor (Regedit.exe).

Add the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\<process_name>.exe

where process_name is the name of the process you want to change the inheritance behavior for.

Assign a DWORD value to this key with the name NodeOptions and data 0x00001000.

Close Registry Editor and restart the computer."

Windows does not currently provide a per service, instance option for starting the service in a specific group. To avoid starting multiple instances of SQL Server on the same processor group use SQL Server 2008 R2 or newer version and establish the proper processor affinity.

SQL Server 2005 SQL Server 2008	Requires SQL Server Affinity Mask to use a CPU in physical NUMA Node = 0 as presented by the operating system.	Use NodeOptions to start SQL Server instance in proper group that presents CPUs from physical NUMA Node = 0 - or - Apply Windows SMC.exe QFE to assign startup group to a specific group containing physical NUMA node = 0
SQL Server 2008 SP3	No longer requires use of CPU in physical NUMA Node = 0	Requires Windows SMC.exe QFE to assign startup to a specific group or you can allow random group startup behavior.
SQL Server 2008 R2	No longer requires CPU in physical NUMA Node = 0	Is group aware so it does not need the NodeOptions or SCM.exe QFE.

Other Helpful References

Bob Dorr - Principal SQL Server Escalation Engineer

A faster CHECKDB – Part I

psssql — Tue, 20 Dec 2011 15:00:29 GMT

Earlier this year I travelled to Japan having the opportunity to visit some of our customers and prospective customers. One feedback I received loud and clear was poor performance when using DBCC CHECKDB as customers have moved into the TB range of databases on a regular basis. I had certainly heard this feedback before from other CSS engineers but not the extent of complaints I heard while in Japan. I had never really investigated these claims of performance issues because I suppose I fell under the same feelings I had and seen for years of “CHECKDB takes as long as it takes”.

On that trip I met Cameron Gardiner from the SQL Customer Advisory Team (SQL CAT). Cameron is an expert with SAP systems and focuses his efforts at Microsoft on SAP running on SQL Server. Cameron spent tine to explain what a problem the performance of CHECKDB had become for his largest SAP accounts using SQL Server.

So I went back to the US determined to investigate the cause and possible solutions for this. The result of this work is now available in the latest cumulative update for SQL Server 2008 R2 customers. You can read the basics of how to apply these two trace flags with this update in this article. A SQL Server 2008 version of these changes will also be available early next year.

I’m on vacation for the rest of the year, but when I come back, I’ll create another blog post (Part II) with the details behind these changes, how we did this, and how this enhancement might help you when using DBCC CHECKDB.

Bob Ward
Microsoft

Exporting via HTML instead of MHTML

Adam W. Saxton — Fri, 09 Dec 2011 13:22:24 GMT

There was a question on Twitter about how to display a report in HTML instead of MHTML due to some browser issues. Based on the fact that it was MHTML, I’m assuming we are talking about exporting a report as the default report view is HTML. First off, if we look at our export options for a report, we see the following:

HTML isn’t an option. One of the reasons or this is because if you export with HTML, and have items in your report such as images, they wouldn’t be included with the export. With MHTML, the binary of the image can be included and will be displayed properly. So, this is the default export option for HTML to make sure we get everything, and the report looks consistent with what is displayed for an on demand report.

That being said, you can change it. This is done within the rsreportserver.config file. Within this file we include the different renderers that Reporting Services will use.

<Render>
    <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport,Microsoft.ReportingServices.DataRendering"/>
    <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering" Visible="false"/>
    <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/>
    <Extension Name="ATOM" Type="Microsoft.ReportingServices.Rendering.DataRenderer.AtomDataReport,Microsoft.ReportingServices.DataRendering" Visible="false"/>
    <Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering"/>
    <Extension Name="RGDI" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.RGDIRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false"/>
    <Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
    <Extension Name="MHTML" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"/>
    <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"/>
    <Extension Name="RPL" Type="Microsoft.ReportingServices.Rendering.RPLRendering.RPLRenderer,Microsoft.ReportingServices.RPLRendering" Visible="false" LogAllExecutionRequests="false"/>
    <Extension Name="IMAGE" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageRenderer,Microsoft.ReportingServices.ImageRendering"/>
    <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
</Render>

One of the properties that you can see is the Visible property. Some of these are set to false by default. This actually lets Reporting Services know if this should be a visible export option. One of the items is for the HTML4.0 renderer.

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

By setting visible to true for this item, it then appears as an export option.

This should work good for a subscription operation, but if you just hit export from the browser, it will just pop it up in a new tab as HTML is an accepted format. So, you won’t get the prompt to download. From a browser perspective, you can also get a clean HTML version of the page via URL access:

http://localhost/ReportServer?%2fHelloWorld&rs:Command=Render&rs:Format=HTML4.0&rc:toolbar=false

In this example, you don’t even need the Format parameter as HTML4.0 is the default renderer. You can find more information about the available URL access parameters at this page:

Using URL Access Parameters
http://technet.microsoft.com/en-us/library/ms152835.aspx

Adam W. Saxton | Microsoft Escalation Services
http://twitter.com/awsaxton

Distributed Replay for SQL Server 2012

psssql — Tue, 15 Nov 2011 20:41:56 GMT

I recently had a lengthy exchange on DReplay with Jonathan Kehayias (SQL MVP). From this exchange I filed several work items with the SQL Server development team to help install and setup DReplay easier in the future.

Jonathan has started a series of blog posts on his experiences that I would only be copying to place on this blog. I suggest you read his series to assist you with your DReplay activities as well.

http://sqlskills.com/blogs/jonathan/post/Installing-and-Configuring-SQL-Server-2012-Distributed-Replay.aspx

Bob Dorr - Principal SQL Server Escalation Engineer

SQL Server: Clarifying The NUMA Configuration Information

psssql — Fri, 11 Nov 2011 17:59:16 GMT

The increased number of cores per socket is driving NUMA designs and in SQL Server support we are seeing more NUMA machines and less pure SMP machines. For whatever reason over the past 2 weeks I have fielded a bunch of questions around NUMA and the information is good for everyone to know so I will try to share it here.

There are various levels of NUMA configurations that can make the this entire discussion complicated. I will try to point some of these things out here as well.

How is the Operating System Presenting NUMA Configuration To SQL Server?

Start with the Windows Task Manager | Process Tab.

Select a process, Right Mouse | Set Affinity -- the following dialog is presented showing you the Processor Groups (K-Group), Nodes and CPUs on the machine.

This is the layout presented to SQL Server.

Windows Resource Monitor | CPU Tab shows NUMA information as well.

Reference: http://blogs.technet.com/b/yongrhee/archive/2011/01/04/how-to-pull-the-information-that-resource-monitor-resmon-exe-provides.aspx

MSINFO32

Information presented in MSINFO does not contain NUMA associations. Here is an example from my 2 socket system with only a single memory node.

Here is an example of my 2 socket system, single memory node but configured using bcdedit /groupsize to create 2 logical groups on my system for testing. The MSINFO32 output looks the same and you can't tell NUMA information from it so don't rely on it for NUMA configuration information.

Issue: The problem with all the previous Windows utilities is that it might not be showing the physical layout presented by the hardware. You may have to go to the BIOS or use the CPUID instruction(s) to determine the physical layout.

Windows does allow configuration of /groupsize for logical testing (http://msdn.microsoft.com/en-us/library/ff564483(v=VS.85).aspx using BCDEDIT or manual establishment in the registry http://support.microsoft.com/kb/2506384. However, it is rare to see these in use on a production system.

API Reference(s)

GetNumaHighestNodeNumber
GetNumaNodeProcessorMask
GetNumaAvailableMemoryNode

SQL Server's View

When SQL Server starts up is outputs the NUMA information in the error log detailing its view of NUMA and provides DMV outputs to show the information as well.

Here is the output from my 2 Processor Group system. A NUMA node can't span a processor group so I am assured to have 2 NUMA nodes with CPUs associated with each node.

2011-11-09 12:38:01.38 Server Node configuration: node 0: CPU mask: 0x000000000000000f:1 Active CPU mask: 0x000000000000000f:1.
2011-11-09 12:38:01.38 Server Node configuration: node 1: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0.

The mask for Node 0 shows 0xf which is 4 CPUs associated with the node. This is correct as it is a 2 socket, 2 core, HT aware system (8 total) 4 assigned to each node. The :1 is the processor group the node is associated with.

Notice that Node 0 is assigned to group 1 on my system. This is a bit different if you look at the low level details. SQL Server swaps its tracking of node 0 to play better with the overall system. Since the system usually allocates things on NUMA node 0 during boot, SQL Server tries to move its node 0 to a subsequent node to avoid contention points. Thus, in my case I see the groups appear to be swapped but that was really just a node swap and instead of SQL Server node 0 being associated with group 0 it is associated with group 1.

SQL Server distinctly tracks memory nodes and scheduler nodes and these don't have to be the same. (See SOFT NUMA for more details).

The memory nodes are aligned, and stay aligned, with the memory locality per CPU presented by the operating system. You can see the memory nodes presented to SQL Server using the following DMV query.

select * from sys.dm_os_memory_nodes

You case the scheduler alignments to the scheduling nodes using the following DMV query.

select * from sys.dm_os_schedulers

I point this out because when you use SQL Server SOFT NUMA configuration it does not change the memory node tracking of SQL Server only the scheduler node behaviors.

I have a non-NUMA system and SQL Server Still Shows NUMA - why?

Answer: Expected - One of the most common questions I get and here is why.

SQL Server (SQL OS) is designed to work with groups, nodes and CPUs. To create an ONLINE scheduler for SQL Server it has to be associated with a scheduling node structure so even on a system that is not-NUMA SQL Server will track the schedulers under a single node. It is just design but it can fool you a bit and some of the wording choices add to the confusion as well.

Here is an example from SQL Server Management Studio (SSMS) from my single socket, dual core system. Notice that 'NumaNode0' is shown. It is just a poor choice of wording and is exposing the same node 0 that SQL Server is using when you look at the DMVs for tracking the CPUs on the system within the scheduling node. Everything is technically correct that we have a single (1) memory bank associated with all the CPUs on the system.

Here is the same SSMS view of my 2 socket, 4 core, 2 memory node system.

Helpful Terms

Group Processor Group	Windows 2008 R2 added the ability to address more than 64 CPUs, called processor groups. (K-Group = Kernel Group) · NUMA node can’t span a K-Group · K-Group is limited to 64 max CPUs
NUMA Node	Hardware concept that associates CPUs with a specific set of memory resources.
CPU	A unit that can process instructions. SQL sees everything as a logical CPU (core, hyper-thread, …) for most operations.
Node Swap	SQL assumes hardware NODE=0 is heavier used by the system so it will often swap NODE=0 with NODE=1 allowing SQL to initialize on the NODE=1. -T8025 can be used to avoid the node swapping behavior
Soft NUMA	A SQL specific configuration allowing nodes to be divided. This can be used to target lazywriter, connectivity and some very specific configurations. Soft NUMA is ONLY FOR Scheduling and Connectivity – Memory locality is not impacted.
Connection Affinity	SQL allows given ports to be bound to a specific NODE or NODE(s) depending on application need.
Round-Robin	New connections are assigned to nodes that the PORT is bound to by round-robin assignment and then weight of scheduler within each node.
ONLINE and OFFLINE Schedulers	A scheduler is ONLINE when its affinity mask is enabled. Let’s say you have a system with 4 CPUs and the affinity mask is 1. You would have 3 offline schedulers (2,3, and 4) and 1 online scheduler (1). SQL Server can create and park the offline schedulers so you can dynamically configure the affinity mask and the schedulers are brought online. If a scheduler is online and you change the affinity mask to make it offline a few things happen. 1. The work currently assigned to the scheduler is allowed to complete 2. No new work is accepted on the scheduler 3. The affinity mask of the threads on the scheduler being taken offline is changed to the other viable schedulers for the instance. This is important because the work is not continuing on the original CPU the affinity was set for but it is sharing the other ONLINE schedulers. So if you have a large process on the original scheduler it can impact time on the shared CPUs until it is complete.
Memory Divided Equally	Memory Per Node = Max Memory Setting / ONLINE NUMA Nodes A NODE is considered ONLINE as long as one of the schedulers on the node is ONLINE. All memory for the max memory scheduler is divided amount the ONLINE nodes. What you don’t want is a situation where you OFFLINE schedulers or configure them strangely. For example Node 1 – 4 Schedulers, Node 2 – 2 schedulers. You would have 4 CPUs using ½ the memory and 2 CPUs using the other ½ the memory.
Trace Flag 8002	The trace flag is used to treat the affinity mask as a group setting. Usually the affinity mask sets the threads on the scheduler to ONLY use one CPU for the matching affinity bit. The trace flag tells SQL OS to treat the mask as a group (process affinity like). Group the bits for the same node toghether and allow any scheduler ONLINE for that node to use any of the CPUs that match the bits. Let’s say you had the following affinity for NODE 0 on the system. 0011 - Use CPU 1 and CPU 2 Without trace flag you would get a scheduler for CPU 1 and a scheduler for CPU 2. The workers on scheduler 1 could only use CPU 1 and the workers on scheduler 2 could only use CPU 2. With the trace flag you get the same scheduler layout but the thread on scheduler 1 and scheduler 2 would set their affinity mask to 11 so they could run on either CPU 1 or CPU 2. This allows you to configure an instance of SQL to use a specific set of CPUs but not lock each scheduler into their respective CPUs, allowing Windows to move the threads on a per CPU resource use need.
Low End NUMA	On some lower end hardware we used to get reported that each CPU has its own NUMA node. This was usually incorrect and when we detected only a single CPU per NODE we would assume NO NUMA. Trace flag 8021 disables this override
ALTER SERVER	Added for SQL 2008 R2 to replace the sp_configure affinity mask settings. Once we can support more than 64 CPUs the sp_configure values are not enough to hold the extended affinity mask. Helpful Query: SELECT inf.affinity_type AS [AffinityType], nd.node_state_desc AS [NodeStateDesc], mnd.memory_node_id AS [ID], nd.processor_group AS [GroupID], nd.cpu_affinity_mask AS [CpuIds], nd.online_scheduler_mask AS [CpuAffinityMask] FROM sys.dm_os_memory_nodes AS mnd INNER JOIN sys.dm_os_sys_info AS inf ON 1=1 INNER JOIN (Select SUM (cpu_affinity_mask) as cpu_affinity_mask, SUM(online_scheduler_mask) as online_scheduler_mask, processor_group, node_state_desc, memory_node_id from sys.dm_os_nodes group by memory_node_id, node_state_desc, processor_group) AS nd ON nd.memory_node_id = mnd.memory_node_id ORDER BY ID ASC

References

RML: ReadTrace Appears To Hang at "Doing Post-Load Data Cleanup" Phase

psssql — Fri, 11 Nov 2011 16:16:05 GMT

Keith and I continue to field the question as to why the Post-Load Data Cleanup appears to take a long time (hours) and can cause SQL Server to use large amounts of CPU.

Notes from Keith:

"What that step does it try to correlate stmt-level events with the batch in which they ran, and show plans with the statement. If you capture starting events then all of this can be done at import time (not via the query) because ReadTrace caches the previous batch/rpc starting event and previous sp:stmt starting event and uses those sequence numbers to fill in on the values on the completed events/showplan events during the import itself.

I’ve tried to optimize this post load import query about half a dozen times now, and invariably it gets better for certain types of scenarios and worse for others. The best solution is to capture the starting events and avoid ragged trace starting point.

The other option is to use the ReadTrace command line parameter –T22 which skips ALL of that processing (including things like indexing), but some of it could be done by manually calling a subset of the same procedures that ReadTrace uses in this step, skipping the one to the Postload fixups.

Even then, some of the reporting features may not work correctly because the event association would not be set correctly.

Bob Dorr / Keith Elmore

Error 1803 and model size change in SQL Server 2012

psssql — Sat, 29 Oct 2011 17:05:32 GMT

Recently I encountered error 1803 when working on SQL Server 2012. The script I ran against a SQL Server 2012 instance was

CREATE DATABASE [suspect_db] ON PRIMARY

( NAME = N'suspect_db', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'suspect_db_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

I was confident I used this same script successfully on previous versions of SQL Server. Therefore, I examined the error message and the script properly:

Msg 1803, Level 16, State 1, Line 3

The CREATE DATABASE statement failed. The primary file must be at least 3 MB to accommodate a copy of the model database.

Then I went and compared the physical size of model and noticed that the size changed between SQL Server 2012 and previous versions. Here is a comparison of the sizes:

SQL Server version	Physical file size (bytes)		sp_spaceused information
SQL Server version	model.mdf	modellog.ldf	reserved	data	index_size	unused
2000	655,360	524,288	528 KB	144 KB	280 KB	104 KB
2005	1,245,184	524,288	1136 KB	472 KB	560 KB	104 KB
2008	1,310,720	524,288	1200 KB	472 KB	624 KB	104 KB
2008 R2	1,310,720	524,288	1216 KB	512 KB	632 KB	72 KB
2012	2,162,688	524,288	2096 KB	792 KB	1080 KB	224 KB

So, the next obvious question is - Why this change now?

When the SQL Server product team ships new features or enhancements to existing features, these involve changes to the core metadata. These might be in the form of new system tables, views, stored procedures and other objects. In some occasions, these changes may be server wide that you just make the change to the associated catalog tables in the msdb or master database. If this is a change that needs to be implemented in every database, then you will see the effect in the model database. Since we added several new exciting features in SQL Server 2012, we needed to add supporting system tables in every database. Therefore, those need to be persisted in model database first. Depending upon on the number of system tables you add and the available free space, the data file needs to grow. That is what happened in SQL 2012. New stored procedures and system wide views are normally implemented in the Resource database and installed as part of the setup.

If you have scripts where you specify the initial size of the database [especially SQL Express], make sure to consider this factor when migrating applications to SQL Server 2012.

Here is the query I used to find out what system tables were added in each version of SQL Server:

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.hobt_id)

where au.type in ( 1 , 3 )

union

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.partition_id)

where au.type in ( 2 )

The output I got from different versions is shown below with the changes highlighted. As you can see these system tables correspond to the new features introduced in SQL Server 2012 [e.g. AlwaysOn, Contained database, FileTable].

SQL 2012					SQL 2008 R2
object_name	index_id	rows	total_pages	size_bytes	object_name	index_id	rows	total_pages	size_bytes
filestream_tombstone_2073058421	1	0	0	0	filestream_tombstone_2073058421	1	0	0	0
filestream_tombstone_2073058421	2	0	0	0	filestream_tombstone_2073058421	2	0	0	0
filetable_updates_2105058535	1	0	0	0
queue_messages_1977058079	1	0	0	0	queue_messages_1977058079	1	0	0	0
queue_messages_1977058079	2	0	0	0	queue_messages_1977058079	2	0	0	0
queue_messages_2009058193	1	0	0	0	queue_messages_2009058193	1	0	0	0
queue_messages_2009058193	2	0	0	0	queue_messages_2009058193	2	0	0	0
queue_messages_2041058307	1	0	0	0	queue_messages_2041058307	1	0	0	0
queue_messages_2041058307	2	0	0	0	queue_messages_2041058307	2	0	0	0
sysallocunits	1	138	4	32,768	sysallocunits	1	103	4	32,768
sysallocunits	2	138	2	16,384	sysallocunits	2	103	2	16,384
sysasymkeys	1	0	0	0	sysasymkeys	1	0	0	0
sysasymkeys	2	0	0	0	sysasymkeys	2	0	0	0
sysasymkeys	3	0	0	0	sysasymkeys	3	0	0	0
sysaudacts	1	0	0	0	sysaudacts	1	0	0	0
sysbinobjs	1	23	2	16,384	sysbinobjs	1	23	2	16,384
sysbinobjs	2	23	2	16,384	sysbinobjs	2	23	2	16,384
sysbinsubobjs	1	3	2	16,384	sysbinsubobjs	1	3	2	16,384
sysbinsubobjs	2	3	2	16,384	sysbinsubobjs	2	3	2	16,384
sysbrickfiles	1	0	0	0
syscerts	1	0	0	0	syscerts	1	0	0	0
syscerts	2	0	0	0	syscerts	2	0	0	0
syscerts	3	0	0	0	syscerts	3	0	0	0
syscerts	4	0	0	0	syscerts	4	0	0	0
syschildinsts	1	0	0	0
sysclones	1	0	0	0
sysclsobjs	1	16	2	16,384	sysclsobjs	1	16	2	16,384
sysclsobjs	2	16	2	16,384	sysclsobjs	2	16	2	16,384
syscolpars	1	694	17	139,264	syscolpars	1	483	16	131,072
syscolpars	2	694	7	57,344	syscolpars	2	483	5	40,960
syscommittab	1	0	0	0	syscommittab	1	0	0	0
syscommittab	2	0	0	0	syscommittab	2	0	0	0
syscompfragments	1	0	0	0	syscompfragments	1	0	0	0
sysconvgroup	1	0	0	0	sysconvgroup	1	0	0	0
syscscolsegments	1	0	0	0
syscsdictionaries	1	0	0	0
sysdbfiles	1	2	2	16,384
sysdbfrag	1	0	0	0
sysdbfrag	2	0	0	0
sysdbreg	1	0	0	0
sysdbreg	2	0	0	0
sysdbreg	3	0	0	0
sysdercv	1	0	0	0	sysdercv	1	0	0	0
sysdesend	1	0	0	0	sysdesend	1	0	0	0
sysendpts	1	0	0	0
sysendpts	2	0	0	0
sysfgfrag	1	0	0	0	sysfgfrag	1	2	2	16,384
sysfiles1	0	2	2	16,384	sysfiles1	0	2	2	16,384
sysfoqueues	1	0	0	0
sysfos	1	0	0	0
sysfos	2	0	0	0
sysftinds	1	0	0	0	sysftinds	1	0	0	0
sysftproperties	1	0	0	0
sysftproperties	2	0	0	0
sysftproperties	3	0	0	0
sysftsemanticsdb	1	0	0	0
sysftstops	1	0	0	0	sysftstops	1	0	0	0
sysguidrefs	1	0	0	0	sysguidrefs	1	0	0	0
sysguidrefs	2	0	0	0	sysguidrefs	2	0	0	0


sysidxstats	1	180	4	32,768	sysidxstats	1	123	4	32,768
sysidxstats	2	180	2	16,384	sysidxstats	2	123	2	16,384
sysiscols	1	367	4	32,768	sysiscols	1	275	4	32,768
sysiscols	2	367	2	16,384	sysiscols	2	275	2	16,384
syslnklgns	1	0	0	0
sysmultiobjrefs	1	107	2	16,384	sysmultiobjrefs	1	106	2	16,384
sysmultiobjrefs	2	107	2	16,384	sysmultiobjrefs	2	106	2	16,384
sysnsobjs	1	1	2	16,384	sysnsobjs	1	1	2	16,384
sysnsobjs	2	1	2	16,384	sysnsobjs	2	1	2	16,384
sysobjkeycrypts	1	0	0	0	sysobjkeycrypts	1	0	0	0
sysobjvalues	1	187	5	40,960	sysobjvalues	1	125	3	24,576
sysobjvalues	1	187	25	204,800	sysobjvalues	1	125	25	204,800
sysowners	1	14	2	16,384	sysowners	1	14	2	16,384
sysowners	2	14	2	16,384	sysowners	2	14	2	16,384
sysowners	3	14	2	16,384	sysowners	3	14	2	16,384
sysphfg	1	1	2	16,384	sysphfg	1	1	2	16,384
syspriorities	1	0	0	0	syspriorities	1	0	0	0
syspriorities	2	0	0	0	syspriorities	2	0	0	0
syspriorities	3	0	0	0	syspriorities	3	0	0	0
sysprivs	1	137	2	16,384	sysprivs	1	130	2	16,384
syspru	1	0	0	0
sysprufiles	1	2	2	16,384	sysprufiles	1	2	2	16,384
sysqnames	1	98	2	16,384	sysqnames	1	97	2	16,384
sysqnames	2	98	2	16,384	sysqnames	2	97	2	16,384
sysremsvcbinds	1	0	0	0	sysremsvcbinds	1	0	0	0
sysremsvcbinds	2	0	0	0	sysremsvcbinds	2	0	0	0
sysremsvcbinds	3	0	0	0	sysremsvcbinds	3	0	0	0
sysrmtlgns	1	0	0	0

sysrowsetrefs	1	0	0	0	sysrowsetrefs	1	0	0	0
sysrowsets	1	124	2	16,384	sysrowsets	1	91	2	16,384
sysrscols	1	870	17	139,264	sysrscols	1	632	9	73,728
sysrts	1	1	2	16,384	sysrts	1	1	2	16,384
sysrts	2	1	2	16,384	sysrts	2	1	2	16,384
sysrts	3	1	2	16,384	sysrts	3	1	2	16,384
sysscalartypes	1	34	2	16,384	sysscalartypes	1	34	2	16,384
sysscalartypes	2	34	2	16,384	sysscalartypes	2	34	2	16,384
sysscalartypes	3	34	2	16,384	sysscalartypes	3	34	2	16,384
sysschobjs	1	2063	33	270,336	sysschobjs	1	53	2	16,384
sysschobjs	2	2063	33	270,336	sysschobjs	2	53	2	16,384
sysschobjs	3	2063	33	270,336	sysschobjs	3	53	2	16,384
sysschobjs	4	2063	6	49,152	sysschobjs	4	53	2	16,384
sysseobjvalues	1	0	0	0

syssingleobjrefs	1	155	2	16,384	syssingleobjrefs	1	146	2	16,384
syssingleobjrefs	2	155	2	16,384	syssingleobjrefs	2	146	2	16,384
syssoftobjrefs	1	0	0	0	syssoftobjrefs	1	0	0	0
syssoftobjrefs	2	0	0	0	syssoftobjrefs	2	0	0	0
syssqlguides	1	0	0	0	syssqlguides	1	0	0	0
syssqlguides	2	0	0	0	syssqlguides	2	0	0	0
syssqlguides	3	0	0	0	syssqlguides	3	0	0	0
systypedsubobjs	1	0	0	0	systypedsubobjs	1	0	0	0
systypedsubobjs	2	0	0	0	systypedsubobjs	2	0	0	0
sysusermsgs	1	0	0	0
syswebmethods	1	0	0	0
sysxlgns	1	0	0	0
sysxlgns	2	0	0	0
sysxlgns	3	0	0	0
sysxmitbody	1	0	0	0
sysxmitqueue	1	0	0	0	sysxmitqueue	1	0	0	0
sysxmlcomponent	1	100	2	16,384	sysxmlcomponent	1	99	2	16,384
sysxmlcomponent	2	100	2	16,384	sysxmlcomponent	2	99	2	16,384
sysxmlfacet	1	112	2	16,384	sysxmlfacet	1	112	2	16,384
sysxmlplacement	1	19	2	16,384	sysxmlplacement	1	18	2	16,384
sysxmlplacement	2	19	2	16,384	sysxmlplacement	2	18	2	16,384
sysxprops	1	0	0	0	sysxprops	1	0	0	0
sysxsrvs	1	0	0	0
sysxsrvs	2	0	0	0
			SUM	2,146,304				SUM	1,245,184

SQL 2008					SQL 2005
object_name	index_id	rows	total_pages	size_bytes	object_name	index_id	rows	total_pages	size_bytes
filestream_tombstone_2073058421	1	0	0	0					0
filestream_tombstone_2073058421	2	0	0	0					0
				0					0
queue_messages_1977058079	1	0	0	0	queue_messages_1977058079	1	0	0	0
queue_messages_1977058079	2	0	0	0	queue_messages_1977058079	2	0	0	0
queue_messages_2009058193	1	0	0	0	queue_messages_2009058193	1	0	0	0
queue_messages_2009058193	2	0	0	0	queue_messages_2009058193	2	0	0	0
queue_messages_2041058307	1	0	0	0	queue_messages_2041058307	1	0	0	0
queue_messages_2041058307	2	0	0	0	queue_messages_2041058307	2	0	0	0
sysallocunits	1	103	4	32,768	sysallocunits	1	89	2	16,384
sysallocunits	2	103	2	16,384
sysasymkeys	1	0	0	0	sysasymkeys	1	0	0	0
sysasymkeys	2	0	0	0	sysasymkeys	2	0	0	0
sysasymkeys	3	0	0	0	sysasymkeys	3	0	0	0
sysaudacts	1	0	0	0					0
sysbinobjs	1	23	2	16,384	sysbinobjs	1	23	2	16,384
sysbinobjs	2	23	2	16,384	sysbinobjs	2	23	2	16,384
sysbinsubobjs	1	3	2	16,384	sysbinsubobjs	1	0	0	0
sysbinsubobjs	2	3	2	16,384	sysbinsubobjs	2	0	0	0
				0					0
syscerts	1	0	0	0	syscerts	1	0	0	0
syscerts	2	0	0	0	syscerts	2	0	0	0
syscerts	3	0	0	0	syscerts	3	0	0	0
syscerts	4	0	0	0	syscerts	4	0	0	0
				0					0
				0					0
sysclsobjs	1	16	2	16,384	sysclsobjs	1	14	2	16,384
sysclsobjs	2	16	2	16,384	sysclsobjs	2	14	2	16,384
syscolpars	1	483	16	131,072	syscolpars	1	419	16	131,072
syscolpars	2	483	5	40,960	syscolpars	2	419	4	32,768
syscommittab	1	0	0	0					0
syscommittab	2	0	0	0					0
syscompfragments	1	0	0	0					0
sysconvgroup	1	0	0	0	sysconvgroup	1	0	0	0
				0					0
				0					0
				0	sysdbfiles	1	2	2	16,384
				0					0
				0					0
				0					0
				0					0
				0					0
sysdercv	1	0	0	0	sysdercv	1	0	0	0
sysdesend	1	0	0	0	sysdesend	1	0	0	0
				0					0
				0					0
sysfgfrag	1	2	2	16,384					0
sysfiles1	0	2	2	16,384	sysfiles1	0	2	2	16,384
				0					0
				0					0
				0					0
sysftinds	1	0	0	0	sysftinds	1	0	0	0
				0					0
				0					0
				0					0
				0					0
sysftstops	1	0	0	0					0
sysguidrefs	1	0	0	0	sysguidrefs	1	0	0	0
sysguidrefs	2	0	0	0	sysguidrefs	2	0	0	0
					syshobtcolumns	1	538	7	57,344
					syshobts	1	78	2	16,384
sysidxstats	1	117	2	16,384	sysidxstats	1	102	2	16,384
sysidxstats	2	117	2	16,384	sysidxstats	2	102	2	16,384
sysiscols	1	269	4	32,768	sysiscols	1	216	2	16,384
sysiscols	2	269	2	16,384					0
				0					0
sysmultiobjrefs	1	106	2	16,384	sysmultiobjrefs	1	102	2	16,384
sysmultiobjrefs	2	106	2	16,384	sysmultiobjrefs	2	102	2	16,384
sysnsobjs	1	1	2	16,384	sysnsobjs	1	1	2	16,384
sysnsobjs	2	1	2	16,384	sysnsobjs	2	1	2	16,384
sysobjkeycrypts	1	0	0	0	sysobjkeycrypts	1	0	0	0
sysobjvalues	1	119	3	24,576	sysobjvalues	1	102	3	24,576
sysobjvalues	1	119	25	204,800	sysobjvalues	1	102	25	204,800
sysowners	1	14	2	16,384	sysowners	1	14	2	16,384
sysowners	2	14	2	16,384	sysowners	2	14	2	16,384
sysowners	3	14	2	16,384	sysowners	3	14	2	16,384
sysphfg	1	1	2	16,384					0
syspriorities	1	0	0	0					0
syspriorities	2	0	0	0					0
syspriorities	3	0	0	0					0
sysprivs	1	130	2	16,384	sysprivs	1	120	2	16,384
				0					0
sysprufiles	1	2	2	16,384					0
sysqnames	1	97	2	16,384	sysqnames	1	91	2	16,384
sysqnames	2	97	2	16,384	sysqnames	2	91	2	16,384
sysremsvcbinds	1	0	0	0	sysremsvcbinds	1	0	0	0
sysremsvcbinds	2	0	0	0	sysremsvcbinds	2	0	0	0
sysremsvcbinds	3	0	0	0	sysremsvcbinds	3	0	0	0
				0					0
					sysrowsetcolumns	1	538	7	57,344
sysrowsetrefs	1	0	0	0	sysrowsetrefs	1	0	0	0
sysrowsets	1	91	2	16,384	sysrowsets	1	78	2	16,384
sysrscols	1	632	9	73,728					0
sysrts	1	1	2	16,384	sysrts	1	1	2	16,384
sysrts	2	1	2	16,384	sysrts	2	1	2	16,384
sysrts	3	1	2	16,384	sysrts	3	1	2	16,384
sysscalartypes	1	34	2	16,384	sysscalartypes	1	27	2	16,384
sysscalartypes	2	34	2	16,384	sysscalartypes	2	27	2	16,384
sysscalartypes	3	34	2	16,384	sysscalartypes	3	27	2	16,384
sysschobjs	1	53	2	16,384	sysschobjs	1	47	2	16,384
sysschobjs	2	53	2	16,384	sysschobjs	2	47	2	16,384
sysschobjs	3	53	2	16,384	sysschobjs	3	47	2	16,384
sysschobjs	4	53	2	16,384	sysschobjs	4	47	2	16,384
				0					0
					sysserefs	1	89	2	16,384
syssingleobjrefs	1	146	2	16,384	syssingleobjrefs	1	133	2	16,384
syssingleobjrefs	2	146	2	16,384	syssingleobjrefs	2	133	2	16,384
syssoftobjrefs	1	0	0	0					0
syssoftobjrefs	2	0	0	0					0
syssqlguides	1	0	0	0	syssqlguides	1	0	0	0
syssqlguides	2	0	0	0	syssqlguides	2	0	0	0
syssqlguides	3	0	0	0	syssqlguides	3	0	0	0
systypedsubobjs	1	0	0	0	systypedsubobjs	1	0	0	0
systypedsubobjs	2	0	0	0	systypedsubobjs	2	0	0	0
				0					0
				0					0
				0					0
				0					0
				0					0
				0					0
sysxmitqueue	1	0	0	0	sysxmitqueue	1	0	0	0
sysxmlcomponent	1	99	2	16,384	sysxmlcomponent	1	93	2	16,384
sysxmlcomponent	2	99	2	16,384	sysxmlcomponent	2	93	2	16,384
sysxmlfacet	1	112	2	16,384	sysxmlfacet	1	97	2	16,384
sysxmlplacement	1	18	2	16,384	sysxmlplacement	1	17	2	16,384
sysxmlplacement	2	18	2	16,384	sysxmlplacement	2	17	2	16,384
sysxprops	1	0	0	0	sysxprops	1	0	0	0
				0					0
				0					0
			SUM	1,228,800				SUM	1,163,264

NOTE: Do not query information directly from system tables or manipulate them. Use the documented and supported interfaces to query and modify the state of various SQL Server entities.

All the information provided above is based on SQL Server 2012 CTP3. It is possible there are changes when the product is released for production.

Thanks

Suresh Kandoth

SQL Server Escalation Services

Microsoft

The week that was PASS 2011 & Moving on…

Adam W. Saxton — Mon, 17 Oct 2011 14:51:00 GMT

This is to recap my week at PASS Summit 2011. This was my 4th US PASS, and every year it is amazing. I really enjoy sharing information with the community as well as getting to meet the people I talk to on Twitter and in other areas. Between Technical and Social Networking, this really is a great event. There were a lot of people at PASS this year as well. This was the first year that I’ve noticed an overflow room for the Key notes and it was filled.

The Big Announcement

The big announcement this year was that we officially launched the Product Branding for SQL Server 2012 and stated that it will be released in first half of next year. Project Crescent also was named Power View. They showed this video during one of the keynotes, and I thought it was a really well made video clip discussing some testimonials of the new features.

(Please visit the site to view this video)

The Sessions

This year we had our normal CSS Pre-Con done by yours truly, as well as a 3 hour main con talk that Bob Ward did. We also had Keith Elmore doing a talk about the new Performance Dashboard that will ship with SQL Server 2012. I found out that Performance Dashboard has been one of the biggest downloads for the Microsoft Download Center. I hadn’t realized that. Cindy Gross also Presented a SQL talk. I wasn’t going to do a main con talk this year, but one of our presenters had to miss the summit due to medical issues and I had a talk on standby that I had presented at SQL Saturday. I found out the Sunday I landed that I would be doing that talk. It was a high level walk through of Reporting Services.

SQL Clinic

The Clinic this year had a great turn out as ever. I think this year, we also had a much better showing on the CSS side. I think this was in part due to the awesome schedule that Chris Wilson and John Gose put together. It seemed to work out really well. We got a lot of different questions spanning the enter product line as usually. The big theme this year was Replication though. on Wednesday it seemed that every other question was something about Replication. I did my part to tout the new SQL AlwaysOn feature that will be in SQL Server 2012.

One attraction that was new this year was the SQL Kinect Demo! It was a fun mock setup of some Management Studio interactions with SQL Server. For example, on one screen, a touchdown like movement would create a table or database. Everyone had a blast with this.

Entertainment

As mentioned, the social events and just time to meet and greet with people attending PASS was great. There was always something going on every night. This year was my first year at a SQL Karaoke event. To illustrate the power of Social, myself and 2 friends arrived at the Karaoke bar. There was really no one there at 9:15 after the last crowd left. We decided to get on twitter and start telling people to come using the #sqlpass and #sqlkaraoke twitter hashtags. Within 15-20 minutes we had about 60-70 people there and had a blast. It was really amazing to witness that.

Food is also a great experience at PASS. At least with some of the restaurants that we end up going to. Like the Crab Pot that just dumps a bunch of sea food on your table. And, the massive deserts that you get!

SQL KILT

Unfortunately, I did not get to participate in the SQL KILT event on Thursday. I really wanted to, but things didn’t work out. This year they had a backup for everyone that didn’t get a kilt though.

Moving on…

I wasn’t really advertising this before, but it came out a lot at the PASS Summit. I have actually left the SQL Support group and am now with the Health Solution’s Support Group. I’m now supporting products like HealthVault and Amalga. There were a lot of reasons that led me to this decision, but one thing I weighed heavily was the SQL Community and the fact that it is really awesome and I have meet a lot of great people. PASS is one of those events that I have come to love and it has felt like a family reunion to me (minus the drama that happens at family reunions!). Our Health Products are really great and I’m looking forward to the new adventure that awaits there. There are a lot of great opportunities with this group and I hope to bring some of the Social and Community Passion with me.

You can continue to follow me at my new blog - http://blogs.msdn.com/hsgsupport. Also, be sure to check out http://www.whatsnextinhealth.com!

Adam W. Saxton | Microsoft HSG Escalation Services
http://twitter.com/awsaxton

Easy JDBC Logging

Evan Basalik — Sun, 16 Oct 2011 19:03:17 GMT

I have been supporting Microsoft’s JDBC driver for almost six years now and the one thing with which I always struggle is getting logging going. JDBC logging is probably some of the most useful logging out there (I only wish BID tracing were so easy to enable and consume!), but for some reason I always struggle getting the correct logging.properties file registered and then figuring out exactly where the log file will be generated. I finally got tired of fighting with it and decided to change both my test code and my command-line to make this much, much easier.

The first thing to recognize is that Java will by default generate the log file in the User.Home folder. Therefore, I decided to output that location as part of my code:

System.out.println("User Home Path: " + System.getProperty("user.home"));

The second thing to do is to manually specify the logging.properties file in the command-line:

java.exe -Djava.util.logging.config.file=c:\temp\logging.properties myJavaClass

Just in case you were wondering, I am using a very simple logging.properties file:

# Specify the handlers to create in the root logger

# (all loggers are children of the root logger)

# The following creates two handlers

handlers = java.util.logging.ConsoleHandler, java.util.logging.FileHandler

# Set the default logging level for the root logger

.level = ALL

# Set the default logging level for new ConsoleHandler instances

java.util.logging.ConsoleHandler.level = INFO

# Set the default logging level for new FileHandler instances

java.util.logging.FileHandler.level = ALL

# Set the default formatter for new ConsoleHandler instances

java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

############################################################

# Facility specific properties.

# Provides extra control for each logger.

############################################################

# For example, set the com.xyz.foo logger to only log SEVERE

# messages:

com.microsoft.sqlserver.jdbc.level=FINEST

com.xyz.foo.level = SEVERE

Now, for the one of the few times where I have needed to generate a JDBC log, it happens on the first time!

Happy logging!

A better solution for the Windows 7 SP1 ADO GUID changes

Evan Basalik — Mon, 03 Oct 2011 00:43:00 GMT

If you are still using ADO in some of your code and tried to upgrade your build machine to Windows 7 SP1, you probably ran into the fact that you cannot run the recompiled program on downlevel OSes unless you modify your references to point to the backward compatible type libraries from http://support.microsoft.com/kb/2517589. Unfortunately, the solution does not work in several scenarios of which the biggest is Visual Basic for Applications (VBA).

The GUID change itself was not really the big problem – in fact, if you have been developing against MDAC/WDAC for a while you probably remember the days when applications would only run on the MDAC version that it was compiled on or higher. When we stopped revisioning MDAC around the Windows 2003 SP2 days, the problem pretty much went away. Until recently…

What happened is that we realized that some of our ADO APIs used platform dependent data types. By this I mean that the 32-bit version of the API used LONG, but the 64-bit version of the same API used LONGLONG. This caused problems when 64-bit applications tried to consume these platform dependent data types and the caller’s data type did not match that of the callee’s. http://support.microsoft.com/kb/983246 discusses a scenario where a VBA macro runs into this exact problem. Unfortunately, we drastically underestimated the number of customers who were recompiling ADO applications on Windows 7 SP1. Even worse, when I say drastically, I really mean DRASTICALLY.

As soon as we realized the magnitude of the problem, we started scrambling to come up with a better solution. At this point, though, our significantly less than ideal first attempt had compounded the problem because it had the potential to spread the changed GUIDs to downlevel OSes. At this point, we made the painful decision to pull http://support.microsoft.com/kb/983246. Yes – we recognized that it would leave some scenarios like VBA without a workable solution, but we deemed that a better option than continuing to spread the modified GUIDs. Although not ideal, our standing recommendations were to use either the backward compatible libraries from http://support.microsoft.com/kb/2517589 or to compile on Windows 7 RTM. While not covering every scenario, it covered the bulk of them and was the best option we could provide without massive re-architecting.

Now, I am happy to announce that we are coming out with a much better solution. We are going to do the following:

Ship the 6.0 type library from Windows 7 RTM via the new type library file msado60.tlb. This will ship for multiple platforms.
Ship a new 6.1 type library (which contains both new and deprecated interfaces) and embed it inside the msado15.dll
Revert back all 2.x type libraries to what they looked like in Windows 7 RTM.

Therefore, the 2.x and 6.0 type libraries can be used for backward compatible purposes, and the 6.1 type library can be used for 64-bit VBA solutions. If you find yourself in the enviable place of not having to do any downlevel OS support, you can migrate entirely to the 6.1 type library even for your non-VBA applications.

Although you can see this fix in action in the Windows 8 Preview we shipped in September, we won’t be able to deliver the Windows 7 SP1 fix until early next year. As you can imagine, we are checking and re-checking everything because we completely understand that the only thing worse than no fix at this point would be another incomplete fix that requires yet another direction shift and another significant delay. Also, we are hard at working making sure some other known issues with the Windows 7 SP1 WDAC build get addressed with this fix as well. These are as follows:

Thread handle leak - http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/68e23681-f6b5-4ed5-b963-e63e34eeac2f
ADOR issues – Reverted back to Windows 7 RTM GUIDs. We recommend anybody using ADOR migrate to the full ADODB object model going forward
JRO failures – JRO references msado15.dll, so once we correct that, JRO should work fine on downlevel Oses
COM+ marshaling scenario - http://social.msdn.microsoft.com/Forums/en-NZ/windowsgeneraldevelopmentissues/thread/0a0fa3b8-9334-45a3-8405-44e6f969c46f

We are still working hard to try to deliver this fix even sooner than early next year, but at this point I am not able to make any promises for an earlier delivery.

After Applying SQL Server 2008 R2 SP1 Error 9013 is logged (The tail of the log for database %ls is being rewritten to match the new sector size of %d bytes …)

psssql — Tue, 27 Sep 2011 16:19:54 GMT

In SQL Server 2008 R2 SP1 we made updates to dynamically accommodate disk drives that present physical sector sizes greater than 512 bytes. In practice, these are generally 4K, physical sector size drives and the SQL 2008 R2 transaction log code will dynamically adjust to the presented physical sector size to accommodate various sector configurations.

To understand this issue completely you have to go back to SQL Server 2000 where we shipped SQL Server's master, model and msdb as if it had been formatted on a 4K, physical sector size drive. This allowed SQL Server to execute on a 4K drive without having to rebuild the master, model or msdb databases.

The formatted sector size (physical sector size of disk where the database was created) is stored in metadata for the database and when the database is restarted (recovered/online/…) we check to see if the data written in the log file is aligned on this formatted sector size boundary. When we detect a situation where the data is not aligned on this boundary we fix up the tail of the log and write a message in the SQL Server error log.

ErrorNumber: 9013
ErrorFormat: The tail of the log for database %ls is being rewritten to match the new sector size of %d bytes. %d bytes at offset %I64d in file %ls will be written.

Customers that have been applying SQL Server 2008 R2 SP1 based builds may encounter the error on restart of the SQL Server service for the master, msdb and model databases.

The work to handle varying sector sizes as done in SQL Server Denali code line and then ported back to the SQL 2008 R2 code line. During the port a specific check was overlooked that results in the error message condition. The database will start writing log records at the physical, disk sector size. This is the proper behavior but for database like master, msdb, and model the logical sector size is not accommodated properly.

Allow me to try to visualize this. In the diagram I show an example of the masterlog.ldf. SQL Server has a logical sector size of 4K but the disk uses 512 byte sectors.

In SQL Server 2008 R2 SP1 we detect the physical sector size of 512 bytes and use that as our write boundary. Prior versions would have used the formatted (logical) sector size in this example - max(physical, logical). We can now write the 1st and 2nd sectors. When we restart SQL the SQL 2008 R2 SP1 log file is checked against the metadata (4K) and it does not align so we log the warning and we write empty data in the next 6 sectors, re-aligning the log write activity on a 4K boundary. But the next write to the log encounters the issue and again writes at a 512 byte boundary so the next restart can trigger the repeat behavior to fix-up the tail of the log.

Note: This does NOT present a risk to the database. SQL Server is writing on sector aligned boundaries, it is just a logical and physical mismatch of the sector size that results in the noisy startup messages.

Bob Dorr - Principal SQL Server Escalation Engineer

RML Questions

psssql — Mon, 26 Sep 2011 15:01:19 GMT

The following questions have surfaced several times recently so I decided to post the answers to assist others.

String is missing proper closing quote near (Char Pos: 0xC1 Byte Pos: 0x182)

This is not a utility bug. It is a command found in the trace that was malformed. For example: select * from tbl where name = ''Bob' <---- This is missing the the trailing quote and the parser logic in RML is pointing it out to you. We keep the integrity of the missing quote so we can actually replay the syntax error.

This is usually an application issue and a place to be very careful about T-SQL security injection.

Let's say that Bob in my example comes from a search box in the application. If I made a mistake and added 'Bob in the search box the application might build the string above and return me a issue near quote. Now I know you are submitting this as dynamic SQL T-SQL and not bound parameters. This means I can enter the following in the search criteria box.

Bob'; drop database production; select '

The application would then build the following command: select * from tbl where 'Bob'; drop database production; select ''

I hit the OK button and your production database might disappear. These are places hackers love - changing this to and RPC event (bound parameters) would be much better for safety. (Look at things like sp_executesql)

Subject: RML Read Trace Error

I am getting the following error while using Read Trace to read SQL 2000 trace files. Do we have any latest version where this problem is fixed? I am using RML Version :9.01.0109 Read Trace Error: [Error: 110003][State: 0][Abs Char: 113][Seq: 0] SYNTAX ERROR: String is missing proper closing quote near (Char Pos: 0xC1 Byte Pos: 0x182)

Embedded NULL Charcters

It is not a bug it is a designed output warning. What I am saying is that EMBEDEDED WITHIN the text of the command there is an actual 0x00 character. This is often unexpected and usually a binding error from a client application. SQL allows 0x00 to be stored so it is not a violation of T-SQL it is more of a bad practice.

For example, it is a way to hide T-SQL injection attacks because a vast majority of applications only display the string up to the first 0x00 character. (C/C++ null termination character)

I could submit the following to your server: select * from good table;(0x00 character added here)drop database Production;

With the right permissions the production database is dropped and you won't see this in any of the traces as the 0x00 hides it from the common display utilities.

Look at the details closer to see if this is something you want to correct in your environment.

Subject: RML Read Trace Error

Do we have any latest version where this bug is fixed?

I am using Version : 9.01.0109

Error Info:

[Error: 110009][State: 10][Abs Char: 0][Seq: 0] WARNING: The input stream contains an embedded NULL (0x0) character near

Bob Dorr, Microsoft SQL Server Escalation Support - Principal Escalation Engineer

Inside the SQL Server Clinic…

psssql — Tue, 20 Sep 2011 02:54:57 GMT

In my last post, I reviewed the Microsoft CSS involvement at the upcoming 2011 SQL Server PASS Summit. One big part as I’ve mentioned is the SQL Server Clinic. I thought you might find it interesting to learn more about exactly what the clinic is and how you can make the best use of it.

Starting on Wednesday, October 12th 2011 (which is the first day of the main conference), we will open up the doors of room 611 (I’m fairly confident we will use this room again) of the Seattle Convention Center. Our hours are “after keynote” each day (we want anyone working the clinic to have the chance to attend the keynote) until about 5-6pm. At any point in time, we could have up to 10-15 Microsoft SQL CAT and CSS engineers in the room waiting to answer your questions or troubleshoot your problems. There is no reservation required. Just show up and walk in.

When talking to our CSS engineers there are some things that you can bring that can help make your experience better. If you have a specific problem to solve, it helps to bring the details .ERRORLOG files, error messages, specific query syntax, or details of your environment. However, if you simply want to bring us your laptop with a remote desktop session back to your office, we will take that too. I can’t tell you how many times I’ve debugged a problem live on a customer’s laptop using remote desktop in the clinic.

Will we have all the answers? No. But most of the engineers in the clinic are willing to do to some research later for things we can’t answer quickly. This is no substitute for opening a case with Microsoft Technical Support. But in almost every situation I’ve seen in the clinic, we usually can provide some direction or tip that gets you closer to solving your issue..

If there is some issue or problem you have been having with SQL Server and you are struggling for an answer, given us a chance in the Clinic. We love the challenge of new problems we have not seen and also the satisfaction of helping someone face to face resolve a tough or difficult issue involving SQL Server.

We hope to see you at the upcoming PASS Summit.

Bob Ward
Microsoft

The SQL PASS Summit comes again to Seattle and CSS will be there…

psssql — Sun, 11 Sep 2011 22:55:10 GMT

For the 9th year, the Microsoft CSS team will speak and meet customers at the SQL PASS Summit, being held this year in Seattle, Washington from October 11th through the 14th.

As in past summits, CSS will have a presence during pre-conference seminars, the main conference, and our infamous SQL Clinic.

I’ll be posting more details on our involvement as we progress closer to the conference, but for now let me give you a quick summary of what CSS will bring to the conference.

Pre-Conference Seminar

Adam Saxton has become of the of the best speakers from CSS to come to recent PASS Summits. We are sending him back again this year to spend a day covering the all important, but misunderstood topic of Kerberos.

Kerberos is something that anyone who must deploy SQL Server wishes they didn’t have to worry about. But unfortunately, it impacts everyone and is not a simple subject. Preventing and troubleshooting Kerberos issues is a very difficult topic even for our own CSS engineers. Adam will not only provide you the internals of how it works but give you practical step-by-step advice on how to configure Kerberos and how to diagnose problems should they occur. Kerberos affects all aspects of SQL Server including the engine, Reporting Services, and Analysis Services. Adam, covers it all including integration with Sharepoint 2010 and PowerPivot

Get the details about Adam’s session at http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1997. If you haven’t signed up for a pre-conference session yet, take a serious look at Adam’s talk.

Main Conference Talks

CSS has always presented in the main conference on a variety of topics but always focused on two types of talks: 1) Deep internals 2) Troubleshooting.

This year will be no different as we will provide talks in the areas of:

Performance showing off the upgraded Performance Dashboard reports for 2011
SQL Azure focusing on important troubleshooting tips and steps you can take to avoid problems building apps for SQL Azure
Performance and engine tuning showing you “all the magic knobs” you may not know about
A deep dive into the internals of tempdb. This will be one of the new “half-day” talks and I fully expect paramedics to be on-call from the brain overload that is likely to take place

SQL Clinic

For the first time last year we partnered with the SQL CAT team for the SQL Server Clinic. It was an amazing success. if you need face to face time with architects or problem solvers, the clinic will be for you. This is something you can take advantage of for no extra charge as part of attending the summit. We love the direct customer interaction so don’t miss a chance to come by and visit. More details on the exact room location at the summit will come later as we get closer to the conference.

So many years ago Ken Henderson encouraged me to get involved with the SQL Community through PASS and it is amazing how much we now contribute. It is a great opportunity for our team to meet customers face to face and to.. “show off a bit” on our skills and knowledge about the SQL Server product.

Stay tuned for more details as we move towards mid October.

Bob Ward
Microsoft

The DMV sys.dm_os_memory_clerks May Show What Appears To Be Duplicate Entries

psssql — Thu, 08 Sep 2011 16:19:22 GMT

In SQL Server 2008 some of the memory based DMVs show memory node id 0 when you might not expect them to. For example you could see the following on a single, node system.

SQL 2008

memory_clerk_address type                      name    memory_node_id
0x0000000003EF6828    MEMORYCLERK_SQLBUFFERPOOL Default 0
0x0000000005040828    MEMORYCLERK_SQLBUFFERPOOL Default 0

SQL 2008 R2

memory_clerk_address type                      name    memory_node_id
0x0000000003EF6828   MEMORYCLERK_SQLBUFFERPOOL Default 0
0x0000000005040828   MEMORYCLERK_SQLBUFFERPOOL Default 64

Notice that the clerk addresses are different so they really do belong to different NUMA nodes. The difference is the exposure of the logical memory node id for the DAC node (64).

In order to support the Dedicated Admin Connection it is given a logical node with a bit of dedicated memory. The node is not associated with any physical NUMA node or CPU and it is given the memory_node_id of 64.

To understand this a bit better you have to understand how SQL considers memory nodes. At the SQLOS level a memory node it represented by the physical layout of CPUs to Memory as presented by the operating system. When looking at SQLOS level DMVs the memory nodes often represent this physical alignment. Since DAC is a logical implementation it is just assigned a node id by SQLOS. In SQL 2008 some of the DMVs did not account for DAC and would output the physical memory node association, making it look like you have duplicate clerks on the same node.

There may also be ways you can see what appears to be duplicates in SQL 2008 R2 if you are using SOFT NUMA. Under SOFT NUMA you can split a physical NUMA node into logical NUMA nodes at the scheduling level for SQL Server. However, at the SQLOS memory node level it still works with the physical memory layout presented by the operating system. This allows SQLOS to provide facilities such as node local memory access to one or more logical nodes that may have been configured with SOFT NUMA.

Bob Dorr - Principal SQL Server Escalation Engineer

SQL Server 2008/2008 R2 on Newer Machines with More Than 8 CPUs Presented per NUMA Node May Need Trace Flag 8048

psssql — Thu, 01 Sep 2011 18:20:37 GMT

Applies To: SQL 2008, 2008 R2 and Denail builds

The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for. The developer may choose a global, CPU, Node, or even worker partitioning scheme. Several of the allocation activities within SQL Server use the CMemPartitioned allocator. This partitions the memory by CPU or NUMA node to increase concurrency and performance.

You can picture CMemPartitioned like a standard heap (it is not a HeapCreate) but this concept is the same. When you create a heap you can specify if you want synchronized assess, default size and other attributes. When the SQL Server developer creates a memory object they indicate that they want things like thread safe access, the partitioning scheme and other options.

The developer creates the object so when a new allocation occurs the behavior is upheld. On the left is a request from a worker against a NODE based memory object. This will use a synchronization object (usually CMEMTHREAD or SOS_SUSPEND_QUEUE type) at the NODE level to allocate memory local to the workers assigned NUMA NODE. On the right is an allocation against a CPU based memory object. This will use a synchronization object at the CPU level to allocate memory local to the workers CPU.

In most cases the CPU based design reduces synchronization collisions the most because of the way SQL OS handles logical scheduling. Preemptive and background tasks make collisions possible but CPU level reduces the frequency greatly. However, going to CPU based partitioning means more overhead to maintain individual CPU access paths and associated memory lists.

The NODE based scheme reduces the overhead to the # of nodes but can slightly increase the collision possibilities and may impact ultimate, performance results for very specific scenarios. I want to caution you the scenarios encountered by Microsoft CSS have been limited to very specific scopes and query patterns.

Newer hardware with multi-core CPUs can present more than 8 CPUs within a single NUMA node. Microsoft has observed that when you approach and exceed 8 CPUs per node the NODE based partitioning may not scale as well for specific query patterns. However, using trace flag 8048 (startup parameter only requiring restart of the SQL Server process) all NODE based partitioning is upgraded to CPU based partitioning. Remember this requires more memory overhead but can provide performance increases on these systems.

HOW DO I KNOW IF I NEED THE TRACE FLAG?

The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spin_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE). Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.

Bob Dorr - Principal SQL Server Escalation Engineer

How compressed is your backup?…

psssql — Thu, 11 Aug 2011 18:55:16 GMT

While working recently with a customer, it was brought to my attention that while SQL Server has a great feature to compress backups (introduced in SQL Server 2008), the space consumed by the backup before it is complete may not be as expected. To be more precise, when you backup a database using compression, the space used by the backup file may actually appear to larger than its “final size”. To be honest, I had not spent much time looking at our compressed backup feature in great detail.

While researching this, I found some people have already discovered this behavior:

http://adventuresinsql.com/2010/02/why-do-my-sql-2008-compressedbackups-shrink/

Also, as it turns out, we had written a KB article explaining this behavior:

http://support.microsoft.com/kb/2001026

You can see from reading these links that what the engine does when backing up a database using compression, it pre-allocates a file that is a percentage of the estimated final size of the database. Then at the end of the backup if the final size needed is less, we shrink the file to the final needed size. So if you monitor the space used of the backup operation, it is possible to observe that initial file size created is larger then when the backup completes. For example, you may backup a 22Gb database with compression. You may see the file size of the backup show up somewhere past 7Gb while the backup is running but end up only being 4Gb when it completes. As the KB article explains, we chose this method to avoid a performance penalty of having to always grow the file as needed to reach its final size.

The customer I was working with said they didn’t mind a small performance penalty (a possible longer duration for the backup operation) so they could save on space and only use up the actual size required for the compressed backup.

Thus comes into play trace flag 3042. As you can see from reading the above blog post, trace flag 3042 bypasses the “pre-allocation algorithm” and grows the file as needed. Up until now, this trace flag was officially undocumented and unsupported. But as you can see in the KB article it is now documented. This was a change we just made in the last few days. Behind the scenes, I was able to work with the SQL Product team (thank you Kevin Farlee and the test team for the engine) to have them run the necessary functional tests to ensure the use of the flag would be supported.

I don’t have any numbers on the possible overall performance hit for using this trace flag. The customer I have worked with has said the overall backup time was slightly slower but not impactful and no significant increase in CPU was observed when using this trace flag.

Consider the update to this article as the official support for the use of the trace flag. This flag is supported in SQL Server 2008, SQL Server 2008 R2, and Denali..

Bob Ward
Microsoft

Stored procedure recompile caused by alter table on temp table

JackLi — Fri, 15 Jul 2011 22:24:31 GMT

Lately we got a customer who called in and reported that a particular statement involving a temp table always got recompiled and caused performance problems.

We have a KB article http://support.microsoft.com/kb/243586 which documents various scenarios that will cause recompile involving temp tables. But none of the conditions seemed to match. After digging deeper, we were able to track down the problem.

Before we discuss the root cause, let’s talk about how you can track down stored procedure recompiles. Starting SQL Server 2005, recompile only occurs at statement level. In other words, one statement recompiling won’t cause the entire stored procedure to recompile.

You can use profiler to watch recompiles. When you choose event, make sure you choose “SQL:StmtRecompile” event (see below).

When you run your stored procedure. You will see “SQL:StmtRecompile” event as shown below if that statement gets recompiled.

Now that we know how to monitor recompiles, let’s talk about what’s expected. If you have a query involving temp table inside a stored procedure, you will always see SQL:StmtRecompile for that query first time you run the procedure. This is a feature called deferred compile. When the procedure is compiled, the query involving temp table is not even compiled. We wait until first time the query is executed. So you will see the recompile at least once for a query involving temp table. What is not expected is that you see the same query being recompiled again and again. That is what this customer complained about.

It turned out that this customer had some alter table statement on the temp table. In the case of temp table, if you have one alter statement on any of the temp tables, statements involving all tables will recompile. The stored procedure below (proc_test) has two temp tables (#t1 and #t2). Though #t2 has no alter table against it, the insert statement involving #t2 will also recompile in addition to the insert on #t1. You can use profiler to monitor yourself. Avoid alter on temp table.

use tempdb
go
create proc proc_test
as
set nocount on
create table #t1 (c1 int)
create table #t2 (c1 int)
insert into #t1 values (1)
insert into #t2 values (1) --this will always recompile even the alter statement is on a different temp table
alter table #t1 add c2 as c1

How It Works: Return codes from SQLIOSim

psssql — Fri, 01 Jul 2011 20:03:51 GMT

I have been asked how to automate SQLIOSim on several occasions. SQLIOSim is a utility to test SQL Server I/O integrity (not performance) patterns against a system without needing to install SQL Server on the system. It ships with SQL Server 2008 and SQL Server 2008 R2 and is located in the BINN directory.

There is a GUI version of SQLIOSim.exe and a command line version SQLIOSim.com.

You can invoke the command line version (.com) from a batch file and check the return value.

0 = No Errors or Warnings encountered and all cycles completed
NOT 0 = Error, Warning or CTRL+C, cycles were not completed

Be sure to set the StopOnError=TRUE in the configuration file you are using. This way when an error is encountered the testing cycles are interrupted and control is returned to the command prompt where you can check the ERRORLEVEL.

You should take a look at the SQL Server High Availability website for more details. Specially the "SQL Server I/O Reliability Program" that outlines a common SQLIOSim stress test.

http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/high-availability.aspx

http://download.microsoft.com/download/6/E/8/6E882A06-B71B-4642-9EB4-D1EA0D6223C8/SQL%20Server%20IO%20Reliability%20Program%20Requirements%20Document.docx (AlwaysOn.SQLIOSim.cfg.ini)

Bob Dorr - Principal SQL Server Escalation Engineer

Server A		Server B
Broker calls activation procedure
Begin Tran (local)
Linked Server Query	Transaction Promoted to DTC	Transaction Imported from Server A
xp_cmdshell	Looped back and blocked Separate Transaction	Blocking