Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,954,808 hits
  • Select GETDATE()

    April 2014
    M T W T F S S

Archive for April, 2014

SQL 2014 Learning Series # 5 – New Feature – Incremental Statistics (Part 1)

Posted by blakhani on April 15, 2014

Many times you might have to update the statistics (index stats or normal stats) with fullscan, either manually or via scheduled job. If the table is large then time taken would be considerably huge because it has to scan each and every record of the table. Let’s assume that we have created partitions on the table and we only modified data for one partition. Earlier version of SQL didn’t offer any choice to update statistics only for one partition. Only choice we had was “FULLSCAN”.

To overcome this, SQL Server 2014 introduced “INCREMENTAL” keyword under Create Statistics. As per books online “When ON, the statistics created are per partition statistics. When OFF, stats are combined for all partitions. The default is OFF.”

To have some sample data, I have downloaded base AdventureWorks sample database from:

I have restored it in SQL Server 2014 and named as AdventureWorks2014. I would be playing around with [AdventureWorks2014].[Production].[TransactionHistoryArchive] table which has transactions from 2005-05-17 to 2007-08-31. I have created quarterly partition.

Use AdventureWorks2014;

CREATE partition FUNCTION [QuarterlyDate](datetime) AS range LEFT FOR VALUES ( 
N'2005-05-01T00:00:00', N'2005-08-01T00:00:00', N'2005-11-01T00:00:00', 
N'2006-02-01T00:00:00', N'2006-05-01T00:00:00', N'2006-08-01T00:00:00', 
N'2006-11-01T00:00:00', N'2007-02-01T00:00:00', N'2007-05-01T00:00:00', 
N'2007-08-01T00:00:00', N'2007-11-01T00:00:00'); 

CREATE partition scheme [TransactionDatePS] AS partition [QuarterlyDate] TO ( 

CREATE TABLE Production.TransactionHistoryArchive_IncrementalDemo(
    TransactionID int NOT NULL,
    ProductID int NOT NULL,
    ReferenceOrderID int NOT NULL,
    ReferenceOrderLineID int NOT NULL, 
    TransactionDate datetime NOT NULL,
    TransactionType nchar(1) NOT NULL,
    Quantity int NOT NULL,
    ActualCost money NOT NULL,
    ModifiedDate datetime NOT NULL)
ON TransactionDatePS(TransactionDate);

Now, I am going to populate partitioned table. To show before and after effect, we would populate only one partition at this point. Since our PF is defined as “LFET” which mean boundary value would go to left side. If I have to populate 8th partition, the range would be TransactionDate > N’2006-11-01T00:00:00′ and TransactionDate <= N’2007-02-01T00:00:00′. Here is the query to dump data into newly created table.

INSERT INTO Production.TransactionHistoryArchive_IncrementalDemo
SELECT * FROM Production.TransactionHistoryArchive
WHERE TransactionDate > N'2006-11-01T00:00:00' and 
TransactionDate <= N'2007-02-01T00:00:00'

(10324 row(s) affected). Now, let’s look at partitions

SELECT * FROM sys.partitions
  WHERE object_id = OBJECT_ID('Production.TransactionHistoryArchive_IncrementalDemo');



As expected, we have 10324 rows in 8th partition. Use below to create “incremental” statistics. Notice that I have added new clause which is available in SQL Server 2014.

CREATE STATISTICS IncrementalStatsDemo 
ON Production.TransactionHistoryArchive_IncrementalDemo (TransactionDate) 

Execute DBCC SHOW_STATISTICS command to look at histogram

DBCC SHOW_STATISTICS('Production.TransactionHistoryArchive_IncrementalDemo', IncrementalStatsDemo)
with histogram



Added more rows to different partition

INSERT INTO Production.TransactionHistoryArchive_IncrementalDemo
SELECT * FROM Production.TransactionHistoryArchive
WHERE TransactionDate > N'2007-02-01T00:00:00' and 
TransactionDate <= N'2007-08-01T00:00:00'

(27318 row(s) affected)


UPDATE STATISTICS Production.TransactionHistoryArchive_IncrementalDemo (IncrementalStatsDemo)
with resample ON PARTITIONS(9, 10)

Now, lets look at stats again.


If we compare with earlier histogram image, the highlighted range was at step 81 earlier which has moved to step 60 now and Total number of steps have increased to 192 (as compared to 81 earlier). This means that update statistics command has read those partitions which were specified (9 and 10) and merged with earlier created statistics. As per books online “ON PARTITIONS – Forces the leaf-level statistics covering the partitions specified in the ON PARTITIONS clause to be recomputed, and then merged to build the global statistics. WITH RESAMPLE is required because partition statistics built with different sample rates cannot be merged together.”

There is more to cover because there are various syntax added on different places. In next blog, I would cover those.

Stay tuned.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series | Tagged: , , | 4 Comments »

    Myths of SQL Server: Rollback Service Pack with Resource Database? (MSSQLSystemResource)

    Posted by blakhani on April 10, 2014

    I have been doing a lot of community activities and lately I have learnt that there is a lot of confusion about resource database. I am going to burst major myth which I mentioned in title.

    First, let’s understand why there was a new “hidden” system database introduced in SQL 2005. I am not sure if you have ever seen SQL 2000 Service Pack setup screens. Here is the of the screen where system objects are modified.


    Prior to SQL 2005, all system objects were in master database and they were open for end user to alter by changing “allow updates” configuration using sp_configure. To safe guard system objects from modification (which might cause unexpected behaviors) product modification has been made and system objects definition is moved to a new hidden databases called “resource” database. Another reason of having resource database is to avoid running script to modify system objects during patching of SQL. Rather than running ALTER commands, just replace resource database file. I must point out that running script is one of many steps during upgrade process. (Keep this in mind as I am going to come back to this point later). During patching process (service pack or major product release) SQL Server setup used to drop and create thousands of system objects. It might take around 10 minutes and during that time SQL server is unavailable for production usage. Resource database is introduced to reduce the down time because script execution is now changed to a file copy of resource database.

    Here are some properties of this database.

    • Database ID = 32767
    • Database name = MSSQLSystemResource
    • Data file name = mssqlsystemresource.mdf
    • Log file name = mssqlsystemresource.ldf
    • State = Read Only / Hidden
    • Contains = Pre-created system T-SQL code like Stored procedures, extended procedures, catalog views
    • Does NOT contain user data or user metadata.

    Since it’s a hidden database, we can’t view the objects located in the database. There are two ways to do that (and this is strictly for learning purpose) In real time, you never have to worry about this database.

    • Start SQL in Single User Mode (Refer earlier blog)
    • Attach the MDF and LDF file as user database.


    There has been changes done in various SQL version about the physical location of the files. Since they are MDF and LDF, initially they were kept in DATA folder, along with master database files. but then there have been problems when DBA used to move master database to new location and service pack used to fail. Customers also showed their concern about backup of this database (as the files are visible in data folder). In SQL 2008 onwards, location has been changed and it is now kept in the same location where sqlservr.exe resides.

    SQL Version Location
    SQL Server 2000 No Resource Database
    SQL Server 2005 <drive>:\Program Files\Microsoft SQL Server\MSSQL.<ID>\MSSQL\Data\
    SQL Server 2008 <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_id>\MSSQL\Binn\
    SQL Server 2008 R2 <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_id>\MSSQL\Binn\
    SQL Server 2012 <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_id>\MSSQL\Binn\
    SQL Server 2014 <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_id>\MSSQL\Binn\


    For all practical purposes we should treat resource database files as binaries/DLLs. Since we have MDF and LDF files, we have been calling it as database. Now, if it’s a DLL, how to get version of current resource database? There are two ways.

    SELECT SERVERPROPERTY('ResourceVersion') 'Resource Version';


      SQL Server ERRORLOG also shows this information.


      Now comes the real conversation.

      DBA: Can I use the resource database to uninstall service pack?

      Balmukund: can you please explain more?

      DBA: I have taken copy of mdf and ldf files before applying 2008 service pack 1. After patching is complete, I want to rollback SP1.

      Balmukund: Okay. how would you do it?

      DBA: Stop SQL services, keep back the old files of mssqlsystemresource and start SQL services.

      Balmukund: Oh no. That’s not something you should do. Those files are just like a DLL of a huge SQL Product. By replacing file you would introduce version mismatch between SQLServr.exe and Resource database.

      DBA: But I learned that resource database can be used to rollback service pack.

      Balmukund: No, that information is not correct. During the whole upgrade process replacing file is just one of the step. Who would take care of unregistering DLLs, keeping back old version of files etc?

      DBA: So, I can’t uninstall a Service Pack?

      Balmukund: I didn’t say that. Starting with service packs (Service Pack 1) in SQL Server 2008 you can uninstall them from Add/Remove Programs like any other update. Till SQL 2005, only way was uninstall SQL completely and reinstall again (Refer KB

      DBA: What you would be if resource database is lost?

      Balmukund: There are two possible options. First and the easiest one is to copy the resource database files from another instance that is the same version, service pack, cumulative update (patch level is very important). Second option is to rebuild system databases.

      I truly hope this blog uncovers few facts about hidden database.

    • Cheers,
    • Balmukund Lakhani
    • Twitter @blakhani
    • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
    • Posted in SQL Myths, SQL Server | Tagged: , , , , | 3 Comments »

      SQL 2014 Learning Series # 4 – New Feature – Buffer Pool Extension

      Posted by blakhani on April 8, 2014

      Today I am going to pick up another new feature of SQL Server 2014 called as Buffer Pool Extension (a.k.a. BPE).  As the name suggests, this feature allows DBA to extend buffer pool beyond the size of the memory allocated to SQL Server. Buffer pool is biggest chunk of memory allocated in SQL Server process. BPE can use nonvolatile storage devices (like SSD) for increasing amount of memory available for buffer pool consumers. This allows usage of SSD as an intermediate buffer pool pages which would help in getting price advantage over the memory increase on the server. Adding storage device on server is less invasive as compare to adding memory and fetching a page from SSD is faster than fetching it from data file. You can think of this as pagefile for SQL buffer pool only.

      Steps to configure:

      To enable the feature we need provide file path and the size. That’s it? Well, keep in mind that size of BPE should be more than current memory allocation, else we would get below error
      Msg 868, Level 16, State 1, Line 40
      Buffer pool extension size must be larger than the current memory allocation threshold 12784 MB. Buffer pool extension is not enabled.

      Due to above check, I have reduced max server memory on my SQL instance so that I can create the demo.

      EXEC sys.sp_configure N'show advanced options', N'1'  
      EXEC sys.sp_configure N'max server memory (MB)', N'1024'
      EXEC sys.sp_configure N'show advanced options', N'0'  

      using above commands, now the max server memory if 1 GB. Then I ran below command to create BPE file of 2 GB (to avoid error 868)

      ON ( FILENAME = 'F:\BufferPoolExtensionDemo\BPE.bpe' ,SIZE = 2 GB) 

      To get configuration information

      Select * from sys.dm_os_buffer_pool_extension_configuration



      Read state_description carefully. It says “BUFFER POOL EXTENSION CLEAN PAGE CACHING ENABLED”. Clean page is a page in SQL memory which has no write pending. It’s generally the page which has been read from data file and no transaction has done the update of that page. Any modification to clean page would make it “dirty page”, which means there is some write on the page which is not yet written to the data file. Only clean pages can be stored in BPE file.

      As per whitepaper mentioned in blog “The dual write design is that dirty pages evicted from the buffer pool are written both to the SSD and to the database on disks; in effect, treating the SSD as a “write-through” cache for dirty pages”



      Where is my page? memory or BPE file?

      To identify that, we can use DMV sys.dm_os_buffer_descriptors which has additional column introduced called is_in_bpool_extension. Here is the query to show such pages.

      Select    *
      from    sys.dm_os_buffer_descriptors
      where    is_in_bpool_extension = 1


      What if you want to change the file size of BPE? In case we want to change the configuration, the only option is to disable and enable the configuration. There is no ALTER command to modify it. Here is the command to turn it off.



      If we query sys.dm_os_buffer_pool_extension_configuration again, we’ll see new information on our buffer pool extension.



      To monitor. Below XEvents are available in SQL Server 2014 to monitor the functionality of BPE


      Here are various messages which contains text buffer pool extension. (taken from sys.messages catalog view)

      Buffer pool extension “%.*ls” has been initialized successfully with size is %I64d MB.
      Failed to create buffer pool extension of size %I64d MB on path “%.*ls”.
      Buffer pool extension configuration “%.*ls” is in wrong format. The format should be “<extension directory>,<size in GB>”.
      Buffer pool extension size must be larger than the physical memory size %I64d MB. Buffer pool extension is not enabled.
      Attempt to disable buffer pool extension when in state %ls is not allowed.
      Attempt to enable buffer pool extension when in state %ls is not allowed.
      Attempting to allocate %1ld BUF for buffer pool extension for a maximum of %2ld page descriptors.
      Buffer pool extension is only supported on Standard and Enterprise Editions of SQL Server.
      Buffer pool extension is not supported on the %ls architecture.
      Buffer pool extension has been disabled successfully. Deleting buffer pool extension “%.*ls”.
      Buffer pool extension size must be larger than the current memory allocation threshold %I64d MB. Buffer pool extension is not enabled.
      Buffer pool extension “%.*ls” cannot be closed because %ld asynchronous I/Os are outstanding.
      Could not change the value of the ‘BPoolExtensionPath’ property. Operating system error %ls

      Important Points:

      • Since BPE files stores only clean pages, there is no risk of data loss in event of loss of extension file or device which stores the file.
      • Available in Enterprise (developer and evaluation) and standard edition feature. Note that this is only for 64 bit SQL Server.

      I would not recommend putting BPE on spinning media. Since I don’t have SSD, I have used F drive but that should NOT be done in production.

      For more information on the buffer pool extension, please read book online topic Buffer Pool Extension.

      Hope this is helpful.

    • Cheers,
    • Balmukund Lakhani
    • Twitter @blakhani
    • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
    • Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: , , , | 6 Comments »