SQL Server SQL CSS

Analysis Services Thread Pool Changes in SQL Server 2012

Tue, 01/31/2012 - 14:40

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

Categories: SQL Server MS Blogs

Management Studio tricks you may or may not know about

Tue, 01/31/2012 - 10:33

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

Categories: SQL Server MS Blogs

The case of the incorrect page numbers

Sun, 01/29/2012 - 16:46

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.

Categories: SQL Server MS Blogs

The official release of System Center Advisor…

Thu, 01/26/2012 - 22:49

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

Categories: SQL Server MS Blogs

How It Works: sys.dm_tran_session_transactions

Tue, 01/24/2012 - 09:47

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

Categories: SQL Server MS Blogs

How It Works: sys.dm_io_pending_io_requests

Mon, 01/23/2012 - 13:28

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

Categories: SQL Server MS Blogs

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

Mon, 01/23/2012 - 13:00

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

Categories: SQL Server MS Blogs

A faster CHECKDB – Part I

Tue, 12/20/2011 - 08:00

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

Categories: SQL Server MS Blogs

Exporting via HTML instead of MHTML

Fri, 12/09/2011 - 06:22

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.

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.

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

Categories: SQL Server MS Blogs

Distributed Replay for SQL Server 2012

Tue, 11/15/2011 - 13:41

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

Categories: SQL Server MS Blogs

SQL Server: Clarifying The NUMA Configuration Information

Fri, 11/11/2011 - 10:59

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

Categories: SQL Server MS Blogs

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

Fri, 11/11/2011 - 09:16

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

Categories: SQL Server MS Blogs

Error 1803 and model size change in SQL Server 2012

Sat, 10/29/2011 - 10:05

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%)

GO

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

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

filestream_tombstone_2073058421

1

0

filestream_tombstone_2073058421

2

0

filestream_tombstone_2073058421

2

0

filetable_updates_2105058535

1

0

queue_messages_1977058079

1

0

queue_messages_1977058079

1

0

queue_messages_1977058079

2

0

queue_messages_1977058079

2

0

queue_messages_2009058193

1

0

queue_messages_2009058193

1

0

queue_messages_2009058193

2

0

queue_messages_2009058193

2

0

queue_messages_2041058307

1

0

queue_messages_2041058307

1

0

queue_messages_2041058307

2

0

queue_messages_2041058307

2

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

sysasymkeys

1

0

sysasymkeys

2

0

sysasymkeys

2

0

sysasymkeys

3

0

sysasymkeys

3

0

sysaudacts

1

0

sysaudacts

1

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

syscerts

1

0

syscerts

1

0

syscerts

2

0

syscerts

2

0

syscerts

3

0

syscerts

3

0

syscerts

4

0

syscerts

4

0

syschildinsts

1

0

sysclones

1

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

syscommittab

1

0

syscommittab

2

0

syscommittab

2

0

syscompfragments

1

0

syscompfragments

1

0

sysconvgroup

1

0

sysconvgroup

1

0

syscscolsegments

1

0

syscsdictionaries

1

0

sysdbfiles

1

2

16,384

sysdbfrag

1

0

sysdbfrag

2

0

sysdbreg

1

0

sysdbreg

2

0

sysdbreg

3

0

sysdercv

1

0

sysdercv

1

0

sysdesend

1

0

sysdesend

1

0

sysendpts

1

0

sysendpts

2

0

sysfgfrag

1

0

sysfgfrag

1

2

16,384

sysfiles1

0

2

16,384

sysfiles1

0

2

16,384

sysfoqueues

1

0

sysfos

1

0

sysfos

2

0

sysftinds

1

0

sysftinds

1

0

sysftproperties

1

0

sysftproperties

2

0

sysftproperties

3

0

sysftsemanticsdb

1

0

sysftstops

1

0

sysftstops

1

0

sysguidrefs

1

0

sysguidrefs

1

0

sysguidrefs

2

0

sysguidrefs

2

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

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

2

16,384

sysnsobjs

1

2

16,384

sysnsobjs

2

1

2

16,384

sysnsobjs

2

1

2

16,384

sysobjkeycrypts

1

0

sysobjkeycrypts

1

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

2

16,384

sysphfg

1

2

16,384

syspriorities

1

0

syspriorities

1

0

syspriorities

2

0

syspriorities

2

0

syspriorities

3

0

syspriorities

3

0

sysprivs

1

137

2

16,384

sysprivs

1

130

2

16,384

syspru

1

0

sysprufiles

1

2

16,384

sysprufiles

1

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

sysremsvcbinds

1

0

sysremsvcbinds

2

0

sysremsvcbinds

2

0

sysremsvcbinds

3

0

sysremsvcbinds

3

0

sysrmtlgns

1

0

sysrowsetrefs

1

0

sysrowsetrefs

1

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

2

16,384

sysrts

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

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

syssoftobjrefs

1

0

syssoftobjrefs

2

0

syssoftobjrefs

2

0

syssqlguides

1

0

syssqlguides

1

0

syssqlguides

2

0

syssqlguides

2

0

syssqlguides

3

0

syssqlguides

3

0

systypedsubobjs

1

0

systypedsubobjs

1

0

systypedsubobjs

2

0

systypedsubobjs

2

0

sysusermsgs

1

0

syswebmethods

1

0

sysxlgns

1

0

sysxlgns

2

0

sysxlgns

3

0

sysxmitbody

1

0

sysxmitqueue

1

0

sysxmitqueue

1

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

sysxprops

1

0

sysxsrvs

1

0

sysxsrvs

2

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

filestream_tombstone_2073058421

2

0

queue_messages_1977058079

1

0

queue_messages_1977058079

1

0

queue_messages_1977058079

2

0

queue_messages_1977058079

2

0

queue_messages_2009058193

1

0

queue_messages_2009058193

1

0

queue_messages_2009058193

2

0

queue_messages_2009058193

2

0

queue_messages_2041058307

1

0

queue_messages_2041058307

1

0

queue_messages_2041058307

2

0

queue_messages_2041058307

2

0

sysallocunits

1

103

4

32,768

sysallocunits

1

89

2

16,384

sysallocunits

2

103

2

16,384

sysasymkeys

1

0

sysasymkeys

1

0

sysasymkeys

2

0

sysasymkeys

2

0

sysasymkeys

3

0

sysasymkeys

3

0

sysaudacts

1

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

sysbinsubobjs

2

3

2

16,384

sysbinsubobjs

2

0

syscerts

1

0

syscerts

1

0

syscerts

2

0

syscerts

2

0

syscerts

3

0

syscerts

3

0

syscerts

4

0

syscerts

4

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

syscommittab

2

0

syscompfragments

1

0

sysconvgroup

1

0

sysconvgroup

1

0

sysdbfiles

1

2

16,384

0

sysdercv

1

0

sysdercv

1

0

sysdesend

1

0

sysdesend

1

0

sysfgfrag

1

2

16,384

0

sysfiles1

0

2

16,384

sysfiles1

0

2

16,384

0

sysftinds

1

0

sysftinds

1

0

sysftstops

1

0

sysguidrefs

1

0

sysguidrefs

1

0

sysguidrefs

2

0

sysguidrefs

2

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

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

2

16,384

sysnsobjs

1

2

16,384

sysnsobjs

2

1

2

16,384

sysnsobjs

2

1

2

16,384

sysobjkeycrypts

1

0

sysobjkeycrypts

1

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

2

16,384

0

syspriorities

1

0

syspriorities

2

0

syspriorities

3

0

sysprivs

1

130

2

16,384

sysprivs

1

120

2

16,384

0

sysprufiles

1

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

sysremsvcbinds

1

0

sysremsvcbinds

2

0

sysremsvcbinds

2

0

sysremsvcbinds

3

0

sysremsvcbinds

3

0

sysrowsetcolumns

1

538

7

57,344

sysrowsetrefs

1

0

sysrowsetrefs

1

0

sysrowsets

1

91

2

16,384

sysrowsets

1

78

2

16,384

sysrscols

1

632

9

73,728

0

sysrts

1

2

16,384

sysrts

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

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

syssoftobjrefs

2

0

syssqlguides

1

0

syssqlguides

1

0

syssqlguides

2

0

syssqlguides

2

0

syssqlguides

3

0

syssqlguides

3

0

systypedsubobjs

1

0

systypedsubobjs

1

0

systypedsubobjs

2

0

systypedsubobjs

2

0

sysxmitqueue

1

0

sysxmitqueue

1

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

sysxprops

1

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

Categories: SQL Server MS Blogs

The week that was PASS 2011 & Moving on…

Mon, 10/17/2011 - 07:51

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

Categories: SQL Server MS Blogs

Easy JDBC Logging

Sun, 10/16/2011 - 12:03

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!

Categories: SQL Server MS Blogs

SQLWebpedia

Search

Site Content

SQL Server Expert Blogs

SQL Server MS Blogs

Syndicate

Analysis Services Thread Pool Changes in SQL Server 2012

Management Studio tricks you may or may not know about

The case of the incorrect page numbers

The official release of System Center Advisor…

How It Works: sys.dm_tran_session_transactions

How It Works: sys.dm_io_pending_io_requests

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

A faster CHECKDB – Part I

Exporting via HTML instead of MHTML

Distributed Replay for SQL Server 2012

SQL Server: Clarifying The NUMA Configuration Information

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

Error 1803 and model size change in SQL Server 2012

The week that was PASS 2011 & Moving on…

Easy JDBC Logging