Aggregated Posts (sorted by date)
Consolidation Strategy References
Abstract from Carpe Datum (Indexed 2010-03-09):
I have a presentation that I give on SQL Server Consolidation Strategies, and in that presentation I talk about a few links that are useful. Here are some that Ive found feel free to comment on more, or if these links go stale: Consolidation using SQL Server: http://msdn.microsoft.com/en-us/library/ee692366.aspx SQL Server Consolidation Guidance: http://msdn.microsoft.com/en-us/library/ee819082.aspx More referenc...(truncated)...
What SPN do I use and how does it get there?
Abstract from PSS SQL Server Engineers (Indexed 2010-03-09):
This month has turned into another Kerberos Month for me. I had an email discussion regarding SPNs for SQL Server and what we can do to get them created and in a usable state. I thought I would share my response to the questions as it will probably be helpful for someone. Here was the comment that started the conversation. And, by the way, this was actually a good question. I actually see this kind of comment a lot in regards to SPN placement. Not necessarily ...(truncated)...
Its OK to take a Shortcut Sometimes
Abstract from Carpe Datum (Indexed 2010-03-08):
I was working this weekend with a fairly simple Excel spreadsheet, and I had to decompose one cell in it out to three columns in a SQL Server table. There are tools within SQL Server Integration Services (SSIS) that should be able to do that, but I just couldnt find my way around them properly. Im not as familiar with SSIS as I would like to be its just not my day-to-day tool. I was struggling with the split of the cells, since it had several different types and kinds of delimeters for the dat...(truncated)...
SQL Server 2008 R2 UNICODE Compression what happens in the background?
Abstract from MS SQL Server Development Customer Advisory Team (Indexed 2010-03-03):
SQL Server 2008 R2 added a much requested feature: Unicode compression. It addresses the need to compress Unicode strings. It is implemented as part of ROW compression, which was added in SQL 2008. That is; if ROW compression (on SQL 2008 R2) is enabled on a table that contains NCHAR / NVARCHAR datatypes, then the Unicode compression algorithm will kick in for each individual Unicode column.Note: since PAGE compression is a superset, which also includes ROW compression, it will also enable Unico...(truncated)...
Enabling Partition Level Locking in SQL Server 2008
Abstract from MS SQL Server Development Customer Advisory Team (Indexed 2010-03-03):
Author: Thomas Kejser Reviewers: Juergen Thomas, Sanjay Mishra, Stuart Ozer, Lubor Kollar, Kevin Cox, Kun Cheng In this blog, we will provide additional details on a well-hidden feature in SQL Server 2008 partition-level locking. But before we illustrate the feature, heres a bit of background about lock escalation and lock granularities. Background SQL Server has a lock manager that tracks which database objects are locked. Typically, a query will take locks at the row or page level, bu...(truncated)...
Performance tips of using XML data in SQL Server
Abstract from MS SQL Server Development Customer Advisory Team (Indexed 2010-03-01):
Author: Kun ChengReviewers: Peter Carlin, Mike Ruthruff, Thomas Kejser, Nicholas DritsasXML data type is usually used to store semi-structured data with great flexibility and query capabilities. Its a good choice for developing platform agnostic applications to separate storage of complex data from processing at application layer, which understands the complex data relationship. To achieve optimal performance of querying XML data in SQL Server, extra steps need to be taken to ensure query access...(truncated)...
Trouble seeing "Birth of Venus" image in the SSMS spatial results tab?
Abstract from Bob Beauchemins Blog (Indexed 2010-02-28):
Michael J Swart posted a SQL query on his blog that produced a rendition of Botticelli’s Birth of Venus, in the spatial results tab of SQL Server Management Studio when executed. But, when everyone rushed over in anxious anticipation, some folks could execute the query but couldn't see the picture on their 64-bit editions. Here's why... It's an obscure problem with rendering spatially invalid results that surfaced around SP1 timeframe. The map control that produces the tab cann...(truncated)...
PowerShell Version Two Get Continuous Perf Counters
Abstract from Carpe Datum (Indexed 2010-02-25):
In version 2.0 of PowerShell, you can now use a direct command-let (get-Counter) to get at the Performance Monitor counters. For instance, to show the current value of the Processor Percent Time, use this command: Get-Counter 'Processor(*)% Processor Time' The interesting part of get-Counter is that you can add a parameter at the end to keep sampling: Get-Counter 'Processor(*)% Processor Time' - Continuous Which is very useful if youre doing ...(truncated)...
SQL Server 2008 R2 Management Studio Express and SQL Azure
Abstract from SQL Server Manageability Team Blog (Indexed 2010-02-24):
If you want to get started right away using tools like SSMS against SQL Azure, you are in luck. It turns out that the SQL Server 2008 R2 November CTP release includes SSMS Express package for 32 and 64 bit systems. Check out the Tools Only section on...(read more)[Image]
How to take ownership of your local SQL Server 2008 Express
Abstract from SQL Server Express WebLog (Indexed 2010-02-24):
Ward Beattie, one of our developers, has published a nice script thatallows you to get sysadmin privilege to your local SQL Server Express instance.Obviously, you need to have administrative rights on your machine or access toan account that does. The script can be very useful in the following situation:When Visual Studio 2010 RC is installed by one user andbeing used by another user, the second user is unable to create databases,tables and other objects in SQL Server 2008 Express instance that ...(truncated)...
How It Works: SQL Server 2008 NUMA and Foreign Pages
Abstract from PSS SQL Server Engineers (Indexed 2010-02-23):
I have received numerous questions about 'foreign pages' so I have put this post together to answer the questions. The SQL Server buffer pool goes through three (3) distinct states. Initial / Ramp-up This state is established during buffer pool initialization and only transitions once committed buffers reaches the target. Once Committed >= Target the state is transitio...(truncated)...
How to get Microsoft to speak at your event
Abstract from Carpe Datum (Indexed 2010-02-22):
If youre holding a special event, such as a SQL Saturday or a user group, one of the challenges is to get speakers for the event. Now, the best speakers come from the community people who use the product day-in, day-out. They have a wealth of expertise, and many of them are really great presenters. But from time to time you might want to get a Microsoft person to speak at your group or event. Microsoft is a big company, and you can get everything from Marketing (yes, theres a place for that) t...(truncated)...
TCP Offloading again?!
Abstract from PSS SQL Server Engineers (Indexed 2010-02-22):
I have spent probably hundreds of hours on cases involving TCP Offloading and I know most of the signs (intermittent dropped connections, missing traffic in network traces). However, I have to admit I got burned by it the other day and spent several more hours working an issue than I should have. I was working on a server-down case for a financial trading company (in other words, large dollars involved every minute they were down) where the customer was experiencing slow connections to SQ...(truncated)...
Backup compression and Virtual Device Interface (VDI)
Abstract from SQL Server Storage Engine (Indexed 2010-02-21):
People often ask if they can get native SQL Server backup compression using VDI interface. The simple answer is 'yes' but there are few things you need to be aware of as described below. You can get details on VDI by clicking link-to-VDI-Specifications.Within the VDI protocol, the backup application specifies a TSQL backup command. This means that the backup application can use any TSQL command line options including the option 'WITH COMPRESSION'. In this case, the SQL Server will send a pre-co...(truncated)...
Analyzing Query Performance just got easier with SQL Azure.
Abstract from TSQLNet Weblog - Cihangir Biyikoglu (Indexed 2010-02-20):
With the recent update we posted to the SQL Azure service (full details of the update here), the sys.dm_exec_query_stats view is enabled. There is a ton of information about how to use the view out there but in a paragraph You can; Find most frequently executed queries (execution_count) Find queries with most IO cycles (total_physical_reads + total_logical_reads + total_logical_writes) Find queries suffering most from blocking (total_elapsed_time total_worker_time) Find queries with...(truncated)...
Did you start your SQL Server Engine correctly?
Abstract from PSS SQL Server Engineers (Indexed 2010-02-19):
Often we run into situations where there is a need to add a trace flag or a startup parameter other than the default startup parameters that SQL Server uses. What you may notice sometimes is that even after adding these parameters/flags, the desired behavior from those parameters/flags does not occur. We will use the following parameter and trace flag to demonstrate the reasons but this applies to any combination of trace flags and parameters. "-g " This is used to expand the Mem-To-Leave r...(truncated)...
Microsoft CSS once again a big part of SQL European PASS Summit
Abstract from PSS SQL Server Engineers (Indexed 2010-02-17):
Im very excited to be invited back to speak at the SQL European PASS Summit 2010 being held in Neuss, Germany April 21-23rd 2010. This is my 4th PASS Summit to speak in Europe and my 3rd year in a row to speak at the location in Neuss. I always enjoy this conference because of the ability to meet many people in a more 1:1 setting. As in past years, Ill be presenting a day-long Pre-Conference Seminar. But unlike past years, Ill be presenting a new topic I have not done at the previous US PASS C...(truncated)...
Mainstream vs Extended Support and SQL Server 2005 SP4: Can someone explain all of this?.
Abstract from PSS SQL Server Engineers (Indexed 2010-02-17):
Based on community feedback (and believe me it was heard loud and clear), the SQL Server product team announced it will ship one last service pack for SQL Server 2005, SQL Server 2005 Service Pack 4 in the last part of this calendar year 2010. The announcement, found at http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/...(truncated)... also mentions that SQL Server 2008 SP2 will also ship in the 3rd quarter of calendar year 2010. At minimum, SQL Server 2005 SP4 will include all cumul...(truncated)...
Understanding SQL server memory grant
Abstract from Tips, Tricks, and Advice from the SQL Server Query Processing Team (Indexed 2010-02-16):
This article describes how query memory grant works in Microsoft SQL Server. It applies to both SQL2005 and SQL2008. Written by Jay Choe, Microsoft SQL Server Engine.-----------------------------------------------------...(truncated)... memory grant (a.k.a. query work buffer) is a part of server memory used to store temporary row data while sorting and joining rows. It is called "grant" because the server requires those queries to "reserve" before actually using memory. This reservation improves...(truncated)...
Code that Writes Code - A Good Idea or Not?
Abstract from Carpe Datum (Indexed 2010-02-16):
Im a big fan of code that writes code most of the time. For instance, whenever you use the templates feature in SQL Server Management Studio (SSMS) or the Maintenance Wizard, youre using code that writes other code. Theres even a trick of writing Transact-SQL (T-SQL) code that in turn creates other code.But there is a class of code that writes code that Im more cautious about. Whenever a program automatically generates a database schema, I begin to get nervous. No, Im not talking about Entity R...(truncated)...