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

Temporary tables introduction.

Temporary tables are those tables that are intended for temporary use and are not required to be durable. They are stored in the tempdb. There are two types of temporary tables: local and global. Local temporary tables are created with a single hash (#) preceding the table name and global temporary tables have two ##.

Local temporary table data are only accessible to the session that created the table, whereas global temporary table data can be accessed by other sessions. Let’s create a local and global temp table and see how they behave.

I opened up and new query (let’s call this session A) in SSMS and created a local and global temp table respectively:

create table ##temp_global_test(    — Global temporary table
id int,
update_time datetime)

create table #temp_local_test(    — Local temporary table
id int,
update_time datetime)

Now let us insert some data:

insert into ##temp_global_test values(1,current_timestamp)
insert into #temp_local_test values(1,current_timestamp)
go 5

In the current session, session A, run:

select * from ##temp_global_test
select * from #temp_local_test

Now let’s  open up a new query in SSMS without closing session A. Let’s call this new session as session B. In session B run:

select * from ##temp_global_test
select * from #temp_local_test

You will get an error for the second command stating .. Invalid object name ‘#temp_local_test’. This is because the temp table #temp_local_test is local to session A and cannot be queried in session B. ##temp_global_test is a global temp table and can be accessed by both session A and session B. This is the difference between local and global sessions.

Do not close session A or session B. If you expand SSMS>Server\instance>Databases>System Databases>tempdb>Temporary Tables, you will see under the name column: ##temp_global_test and #temp_local_test_____… If you close session B first, you will still see both these tables. If you close session A first, then both these tables are lost even if you had a open session B referencing the global temp table.

SQL server

SQL allocation pages.

The extents in SQL server are managed by special pages in the data file. These files are know as allocation pages. The types of allocation pages are: IAM, GAM, SGAM, PFS, BCM, DCM.

IAM: Index allocation Map. Used for tracking [clustered indexes or heaps]  and/or non-clustered (249 allowed) indexes, and/or text storage. Each IAM tracks 4 GB of data (GAM interval) in a single file. Linked list IAM chains are used to track objects that are contained in different data files, or entities that are divided between the 4 GB boundary. Read More…

GAM: Global allocation Map. Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

SGAM: Shared global allocation Map. Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

PFS: Page free space. Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages. GAM, SGAM, PFS info taken from the post by Robert L. Davis on SQLserverCentral. Detailed info can be found in Paul Randal’s post.

BCM: Bulk changed map. Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit. When in the bulk-logged recover model, the BCM page holds information on which extents in the GAM interval (4GB) have changed since the last full backup.

DCM: Differential changed map. Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

It is this logic that is one of the checks used in the DBCC CHECKDB statement. The rows in a page are not necessarily arranged in their logical order.  The row order is managed by a slot array at the bottom of the page. See the image from BOL. This makes it easier for deletions and inserting into the deleted slots.

For an index of links to similar topics, click here.

I am using this post to consolidate a list of all the information about allocations and pages. Hence the mixed look.