SQL server, SQLServerPedia Syndication

SQLCAT whitepapers.

SQLCAT stands for SQL Customer Advisory Team. The SQLCAT blog contains amazing posts by Microsoft’s SQL engineers back at Redmond. Reading this blog will give you a lot of  intrinsic information about SQL server.

One thing I love reading in particular is the whitepapers released by the team. They contain best practices and real life user scenarios. The steps and settings outlined in these whitepapers are offer deep insights and point to ponder about.

Just reading the abstracts gives be goose bumps, for eg: This white paper explains how Microsoft® adCenter implemented a Microsoft SQL Server® 2008 Analysis ServicesScalable Shared Database on EMC® Symmetrix VMAX™ storage. Leveraging TimeFinder® clones and Enterprise Flash Drives with the read-only feature of SQLServer 2008 Analysis Services allowed adCenter to dramatically scale out OLAP while maintaining SLAs and decreasing system outages. Note that  adCentre is what powers the Ads on Bing search.

Do visit the blog and enjoy. Here is a link to a consolidated index of all the SQLCAL whitepapers.



SQL server, SQLServerPedia Syndication

SQL 2005 installation oddity on multicore processors.

There is an issue with installing SQL 2005 on systems that have an odd (as in not even) number of cores/processors.  The conditions are as follows:

  • The ratio between logical processors and physical sockets is not a power of 2. For example, the computer has a single socket together with a triple-core processor.
  • The number of physical cores is not a power of 2.
  • The total number of CPUs is an odd number. For example, there are seven or nine CPUs.

Note that these issues are fixed in SP2, but the catch 22 is that SQL 2005 setup (non express editions) is not integrated. That is you need to install SQL 2005 first and then upgrade to SP2. So how will you get to SP2 if you cannot install SQL 2005 at all? There are three methods mentioned by Microsoft:

  1. Copy the SQL 2005 setup and SP2 files to local folders. Expand the SP2 package. Point the SQL 2005 setup binary to the expanded package and run setup. Run the SP2 patch. Link.
  2. Temporarily change the number of logical processors to one. Start>Run>Msconfig>BOOT.INI>Advanced options>/NUMPROC=1. Install SQL 2005 and upgrade to SP2. Change back processor count. Link.
  3. Run SQL 2005 setup. When the setup fails, you get a ‘Retry’ option. Replace the Sqlservr.exe and Sqlos.dll files in the BINN folder with the same files from the SP2 package. Retry. Link.

Note: I am not sure if the author meant ‘power of 2’ (1,2,4,8,16,32) or ‘multiple of 2’ (2,4,6,8,10). The reasoning being that I have installed SQL 2005 on a 24 core (physical) machine.

SQL server, SQLServerPedia Syndication

SQL server edition comparisions.

I had created an old post about Enterprise only features of SQL server. As I found out interesting features, I added it to that old post. Today, I found out an MSDN link that has a feature comparison table which summarizes everything I was trying to capture in that post.

The link above is for the 2008 R2 version. You can switch between 2008, 2008 R2, and 2012.

As of 2012, the different editions of SQL server are:

  • Enterprise
  • Business Intelligence
  • Standard
  • Web
  • Express with Advanced Services
  • Express with Tools
  • Express

I thought the PDW was like the top most edition, but apparently, it is marketed as an appliance.

SQL server

Index density and selectivity.

Two terms that used to define an index’s weight/quality are: selectivity and density. I have come across these terms many times and assumed their meaning which was mostly right. To end those assumptions and have a proper explanation, see the example below:

In a company with 1,000 employees, an index on Date of Birth would likely be highly selective—meaning that a query for employees born on a given day should, statistically, never return more than an average of 3 or 4 employees per day. In SQL Server terminology, this index would have a density of .003% (or 3 out of 1,000), which in turn, would translate to an index selectivity of .997 (index density and selectivity are inversely related or proportional.) Essentially, this index would be beneficial in any query used against the birth date of employees, as the data within this index is ‘selective’ or capable of discriminating against different types of results. Within SQL Server, the more selective an index, the greater the chance that it will get used, and the more efficient it will be at returning results in a performant manner.

Full credit for example goes to an excerpt from a whitepaper by Michael K. Campbell

SQL server, SQLServerPedia Syndication

CTE (Common Table Expressions) and sub queries.

Common Table Expressions or CTEs can be used to create temporary result sets based on another query. The CTE is not saved as a DB object and has the same lifetime of the query. CTEs come in handy when a recursive query is required, or when you need to aggregate on top of another select statement.

The CTE is populated by using a Select clause, similarly to how views are defined . Below I show an example of a CTE.

— With CTE
with uo_cte(id,CustomerID,ShoppingListstatus,ShoppingListdate)
SELECT  top 100 Id,CustomerID,ShoppingListStatus,ShoppingListDate FROM UserShoppingList where ShoppingListDate<‘2013-02-10’
SELECT ShoppingListstatus,COUNT(id) from uo_cte
group by ShoppingListstatus

I usually use subqueries to aggregate on top of another select clause. In the example below, I show how the CTE above can be redefined as a subquery.

— With Subquery
SELECT subq1.ShoppingListStatus,COUNT(subq1.Id) from
SELECT  top 100 Id,CustomerID,ShoppingListStatus,ShoppingListDate FROM UserShoppingList where ShoppingListDate<‘2013-02-10’
GROUP by subq1.ShoppingListStatus

The above examples have pretty much the same performance and function. CTE’s have an advantage over using a subquery in that you can use recursion in a CTE. To read more about a recursive CTE, check out this link.

SQL server

Moving a database between SQL instances.

Moving a DB between two SQL instances can be done with a simple backup and restore, but there are a number of dependencies on the DB that are required to be working to have a full cycle connection between the end user and the DB. One example is the server level logins which are not moved with the DB. This data is saved in the master DB and will have to be explicitly copied over and linked with the DB users in the new instance. Another example is the SQL jobs there were running on the first instance. These jobs are saved in MSDB  and need to be recreated on the second instance. There are others like encryption keys, full text settings, etc.

Below is a list of entities/objects that are stored outside the DB:

  • Server configuration settings
  • Credentials
  • Cross-database queries
  • Distributed queries/linked servers
  • Encrypted data
  • User-defined error messages
  • Event notifications and Windows Management Instrumentation (WMI) events (at server level)
  • Extended stored procedures
  • Full-text engine for SQL Server (MSFTESQL) service properties
  • Jobs
  • Logins
  • Permissions
  • Replication settings
  • Service Broker applications
  • Startup procedures
  • Triggers (at server level)

This list was compiled on BOL and the details of these items can be viewed here.