Configuring the instance of SQL Server for Dynamics AX

Improve SQL performance lead us to optimize AX performance, this post is one of posts to achieve that

1- Configuring max degree of parallelism

The max degree of parallelism option is a setting that affects the entire instance of SQL Server. Microsoft Dynamics AX workloads generally perform better when intra-query parallelism is disabled. However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance. Use the following settings when the system performs maintenance activities or an upgrade:

· Before an upgrade to a new release of Microsoft Dynamics AX, or before a large number of maintenance or batch activities, set max degree of parallelism to the smallest of the following values:

· 8

· The number of physical processor cores

· The number of physical processor cores per non-uniform memory access (NUMA) node

· When the Microsoft Dynamics AX database is used in a production environment, set max degree of parallelism to 1.

Use the following statements to set the value of max degree of parallelism.

Examine the output from the second sp_configure 'max degree of parallelism' statement, and confirm that the value has been changed. In the following query, the first sp_configure 'max degree of parallelism' statement sets the value of max degree of parallelism to 1. The second sp_configure 'max degree of parallelism' statement returns a value of 1.

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

EXEC sp_configure 'max degree of parallelism', 1;

RECONFIGURE;

GO

EXEC sp_configure;

For more information, see max degree of parallelism Option. For general guidelines, see Knowledge base article 329204, General guidelines to use to configure the MAXDOP option. For tips from the SQL Server team, visit the SQL Server Relational Engine team's blog, SQL Server Engine Tips.

2- Configuring max server memory

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory is allocated to SQL Server. However, the max server memory option can be useful in some environments. Make sure that sufficient memory is available for the operation of Windows Server. For more information, see Configure SQL Server and storage settings, later in this topic.

If you find that the dynamic allocation of memory adversely affects the operation of Windows Server, adjust the value of max server memory based on the available random access memory (RAM). For more information, see Effects of min and max server memory.

3- Monitoring available memory

Make sure that sufficient memory is available for the operation of Windows Server. For example, make sure that you run a dedicated instance of SQL Server on a server that has at least 4 gigabytes (GB) of memory. If the available memory for the server drops below 500 megabytes (MB) for extended periods, the performance of the server may degrade.

Use the Memory: Available Mbytes performance counter for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods. If the available memory drops below 500 MB frequently or for extended periods, we recommend that you reduce the max server memory setting for SQL Server or increase the physical memory of the server.

Detailed guidance about memory management is beyond the scope of this topic. For more information about how to monitor memory and troubleshoot performance issues, see the Windows Server and SQL Server documentation.

4- Allocating storage for tempdb

We recommend that you determine the total size of the data files and transaction log files that are required for the tempdb database, and that you set a specific value. Do not use automatic growth, or autogrow, setting for space management. Instead, use autogrow as a safety mechanism, so that tempdb can grow if tempdb files use the space that was originally allocated to them. Follow this process to determine the number and placement of data files.

· Determine the number of processors that are available to SQL Server. Unless you are using an affinity mask, this number is same as the total number of processors that you see on the Performance tab of Windows Task Manager. When hyperthreading is not enabled, each processor corresponds to a processor core. Affinity masks and processor cores are beyond the scope of this topic. For more information, see the Windows Server and SQL Server documentation.

· Based on performance testing of the OLTP workload for Microsoft Dynamics AX, we recommend that you maintain one tempdb data file per processor. For more information, see the performance benchmark reports on PartnerSource or CustomerSource.

· Isolate tempdb on dedicated storage, if you can. We recommend that you move the primary data file and log file for tempdb to high-speed storage, if high-speed storage is available. The Microsoft Dynamics AX database runs in read committed snapshot isolation (RCSI) mode. In RCSI mode, row versions are stored in tempdb. By creating multiple files for tempdb data, even if these files reside on the same storage device, you can improve the performance of tempdb operations.

· Determine the size of the tempdb data files and log files. You must create one primary data file and one log file. Determine how many additional, secondary data files you require for the tempdb data. For best results, create data files of equal size. The total number of data files must equal the total number of processor cores. The aggregate size of the primary data file and all other data files must equal the total data size that you determined for the tempdb database.

For more information, see Optimizing tempdb performance.

· Resize the primary data file and log file for tempdb. Move the primary data file and log file to dedicated storage, if dedicated storage is available. The primary tempdb data file cannot be moved while the instance of SQL Server is running. To complete the move, you must use an ALTER DATABASE statement and restart the instance of SQL Server. For more information, see ALTER DATABASE.

Note: The data files and transaction log files for tempdb can reside on the same storage device.

· If space is available on the drive where tempdb files are allocated, do not configure the autogrow property for data files and log files as a percentage. Instead, configure the autogrow property as a specific number of megabytes. If you can, configure the data files and log files to grow by 100 to 500 MB, depending on the available space. Monitor the data files, and when they grow, adjust the original allocation to prevent automatic growth later. If the autogrow property is configured in megabytes instead of as a percentage, the allocation of space is more predictable, and the chance of extremely small or large growth increments is reduced.

· Monitor the tempdb data files and log files to make sure that they are all sized correctly, and that all data files are of equal size. Use SQL Server Management Studio or a transact-SQL query to view the database properties. Verify that all the data files are of equal size, and that they have the same size as the value that you originally provided. If one or more files have grown, adjust the initial size of all files.

Configuring physical storage for SQL server of Dynamics AX

This post provides general recommendations for physical storage. Determine the applicability of these recommendations to your environment. Some storage area network (SAN) vendors may have alternative recommendations that take precedence. Recommendations are listed in order of priority.

• Many factors contribute to optimal I/O performance for a disk. By default, Windows Server 2008 aligns partitions. When you upgrade to Windows Server 2008, preexisting partitions are not automatically aligned and must be manually rebuilt to guarantee optimal performance. Therefore, until you rebuild the migrated partitions, alignment of disk partitions remains a relevant technology.

Check existing disks on the server, and be aware of the differences in the analysis of basic partitions and dynamic volumes. Rebuild the partitions, if you can, and appropriate and create all new partitions based on guidance from the SAN vendor. If the vendor does not provide recommendations, follow the best practices for SQL Server. See Disk Partition Alignment Best Practices for SQL Server.

The partition offset value must be a multiple of the stripe size. In other words, the expression, partition offset / stripe size, must resolve to an integer value.

• Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10. We recommend RAID 10 for these files. Do not use RAID 5.
• Store the data files for the Microsoft Dynamics AX database on separate physical stores from the transaction log files.
• Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.
• Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.

Re-index Dynamics AX database

Our database is incremented by 1 GB daily, and we apply periodic clean up and re-index from dynamics ax client but it is not affect size so much, so we tried using SQL re-index through SQL visual studio but we noticed that the re-index is increased not decreased plus data size of table is increased also although number of records are the same before and after the re-index, any body faced this problem before?

Hint: we are using SQL Cluster (2 Nodes)

Troubleshooting blocked SPIDS in AOS

 

In this blog post I will provide some details on how the AOS server manages it SQL connections and some tips on troubleshooting blocked connections in the database. All of my description is based on the SQL Server backend but similar techniques are applicable for Oracle also, only the DB tools are different. Let’s first start with quick primer on SQL connection management inside Ax.

When connecting to SQL Server, Dynamics uses ODBC APIs to connect to the database. There is a cost in establishing the connection and logging in the user , hence the AOS uses a connection caching mechanism that allows recycling of connections when not in use. Internally, the AOS keeps track of 3 types of connections which are:

· Regular application connection – All application code use this connection.

· RECID connection – There is a dedicated connection to the SystemSequences table. This is used by the RECID allocator inside the AOS.

· Read Only/SysLastValue connection – This is a shared connection that is used for SysLastValue and read only queries.

Note, that the AOS internally distinguishes between these connection types but externally when looking in the database or in the application it is not possible to differentiate between them easily. In fact if a particular connection is cached and then reused again it might end up being reused as a different type of connection than its original type although there are some restrictions on the types when reusing the connections

The connection cache is implemented as a FIFO queue where the connection is stored while it is not being used. By not being in use I mean that there is no active SQL statement from the AOS to the database backend using that connection. The AOS also keeps track of the state of the connections before putting them in the cache for reuse. If the connection is bad due to connectivity errors to the database or any other errors that would cause errors in the future, then that connection will not be reused and it will deleted which will close the connection to the database.

Now let’s see how to look at some of this information from outside the AOS. There are a variety of ways to look at the active connections in a database server. For our discussion we will consider two alternatives, one using the tools provided by SQL Server and the other from within Ax itself. Let’s first look at the option or using SQL Server. My favorite tool for a quick overview of connections is the “Activity Monitor” tool that is available in the SQL Server Management Studio. This tool can be found under the Management folder in the object explorer. There are lots of columns for each active connection but the Process ID (SPID) is the one of interest to us. When you first start an AOS server you will see 2 SPIDS which have “Microsoft Dynamics Ax” under the Application column. These are the connections which are currently opened by the AOS and they could be either active or an inactive connection in the cache.

SQL Server 2005

SQL Server 2008 R2

From inside Ax, you can see some of the above information using the “Online Users Form”. The Client Sessions tab has a column called SPIDs that shows the connections that are currently active on a particular session. I should make you aware that you don’t get to see the whole picture when using this form. To start with you only see the active connections that are assigned to a session. This explains why sometimes you will not see any value in the SPIDs columns for some of the users. Basically it means that the user does not have any active connection to the database at that instant of time. Another limitation is that the SPID will be populated only if the client where you are viewing the form is connected to the same AOS as the other session. So if you have multiple AOS’ in a cluster you will not see all the active SPIDs in the system by opening a single instance of the online users form. You will have to open the form in separate clients each connected to a different AOS.

Now let’s look at how we can use some of the above information to troubleshoot scenarios where sessions are blocked in the database and how to get rectify the situation. You can again start with the Activity Monitor to look lookup the SPID of the blocked session. The activity monitor also gives you the SPID that it blocking any other SPID and also the DB resource that is causing the contention. You can then use the online users form to determine the owner of the session in AX for the blocked and blocking SPIDs. The form provides an ability to terminate an existing session in Ax. But you have to be careful in terminating the correct session. If you try to terminate the blocked session you will see that the online users form changes the status to “Ending – Blocked”. This state indicates that the AOS tried to terminate the session but it was not successful since this session has some open resources and it cannot be safely terminated. One option to solve this situation is to terminate the session that is blocking the other session(s). If the termination is successful, the database connection is closed and this will free up the DB resources for the blocked session(s).

In the online users form when you try to terminate a session sometimes you might see the status change to “Ending – Waiting for AOS”. This happens when you terminate a session that is not in the same AOS as the client that sent the terminate request. In this case the request is placed to the other AOS and it monitors for terminated session in the background and will it terminate it eventually when it processes the request.

In addition to the above techniques there are alternate techniques to troubleshoot blocking in the SQL Server database. You can query the database provided Dynamics Management Views (DMVs) to get more details on the resources that are being consumed or blocked.

I hope you found the above information useful and interesting. Let us know if you would like more information in related areas on how the AOS works and options to manage it. We would also like to hear any suggestions for improvements in any of the areas covered in this topic.

How to shrink tempdb

Imparted from here

There may come a time when you might want to shrink tempdb because it has become too large.
There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post.
So first, we’ll look at the configuration on my server
1
SELECT name, size
2
FROM sys.master_files
3
WHERE database_id = DB_ID(N'tempdb');

name                 size
-------------------- -----------
tempdev              1280
templog              640

(2 row(s) affected)

Note that the size column is listing the size of the file in 8Kb pages. In this instance my “tempdev” file is 10Mb (( 1280 * 8 ) = 10240 kb)

How to shrink tempdb using DBCC SHRINKFILE

The syntax is as follows and the operation does not require a restart of the sql server service.
DBCC SHRINKFILE(logical_filename, size_in_MB)
So I will shrink the file to 5Mb
1
DBCC SHRINKFILE(tempdev, 5);
Which produces the following output and I can see that CurrentSize is now 50% smaller than previously

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
2      1           640         288         176         176

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

name                 size
-------------------- -----------
tempdev              640
templog              640

(2 row(s) affected)

Don’t try and increase filesizes in tempdb using this command because you will see an error. In this example, the attempt was to increase to 50Mb.

Cannot shrink file '1' in database 'tempdb' to 6400 pages as it only contains 640 pages.

How to shrink tempdb using DBCC SHRINKDATABASE

The syntax is as follows and does not require a restart of the SQL Server service:
DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’);
So if the data files in tempdb had enough free space, you could shrink tempdb by running this command to leave 10% of free space at the end of the files:
1
DBCC SHRINKDATABASE(tempdb, 10);

How to shrink tempdb using ALTER DATABASE

As in my post about moving tempdb, you can use the ALTER DATABASE command to perform a tempdb resize. A restart of the SQL Server service will shrink tempdb to its original predefined size but you can also resize tempdb using ALTER DATABASE.
The following script will resize both the log and data file to be 100Mb.
1
USE master;
2
GO
3
ALTER DATABASE tempdb
4
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
5
GO
6
ALTER DATABASE tempdb
7
MODIFY FILE (NAME = templog, SIZE=100Mb);
8
GO

How to shrink tempdb using Management Studio

You can also use Management Studio to resize Tempdb and perform shrink operations by right mouse clicking the tempdb database and choosing Tasks->Shrink.
To resize tempdb, you can set the file sizes by right mouse clicking the tempdb and choosing Properties->Files and setting the sizes there.
Note that with both the ALTER DATABASE and management studio methods, you cannot resize a tempdb file to a particular size if the data contained in the file exceed the size that you are trying to size to.
Should you shrink TempDB?
It is documented in this Microsoft article that it can cause consistency errors in the TempDB database if you perform a shrink operation while the database is in use so please read this carefully and consider whether you can shrink the database by other means, i.e restarting the SQL Server instance which will create a brand new copy of TempDB releasing the disk space.

Naming schema for Microsoft SQL Server software update packages

This post is imparted from Here

Microsoft has adopted a standardized naming schema for all the software update packages for SQL Server that are created and distributed.
A software update package is an executable (.exe or .msi) file that contains one or more files that may be applied to Microsoft SQL Server installations to correct a specific problem. Software update packages are distributed by Microsoft Customer Support Services (CSS) to customers whose computers are affected by a specific problem.
Microsoft has adopted a naming schema for software update packages for the following reasons:

• Creates consistency across Microsoft software update packages
• Easier to search for software updated packages and Knowledge Base articles
• Clear identification of the language and SQL Server version for which the software update package are applicable

Package information and release types

Each software update package that is selected at download time is contained within a self-extracting executable that facilitates easy installation and deployment of the software update package.
SQL Server software update packages typically fall into two major release types:

• GDR (General Distribution Release): GDR releases are reserved for those key fixes identified by SQL Server support to potentially affect a broad customer base.
• Hotfix: Hotfix releases are typically for fixes to isolated issues not affecting a large customer base; while the product is in mainstream support. Hotfix are released in two major types:
  • COD (Critical On Demand) or OD (On Demand): COD or OD releases are reserved for critical customer requests where key business functionality is impaired from the issue encountered. As the nature of the request, these releases do not follow a regular cadence.
  • CU (Cumulative Update): CU releases are non critical requests which provide fixes for isolated issues not affecting key business functionality. The CU releases on a two-month cadence while the product and service pack are in mainstream support.

To learn more about the ISM and the different release types, which SQL Server servicing follows, see the following Microsoft Knowledge Base article:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

Packages and file naming schema

Naming schema for SQL Server software update packages

SQL Server software update packages can be easily identified using the following naming schema.

Software update package name schema

To distinguish between the various software update packages available online, the following schema is employed:

<product name or product program name>_<SP number or RTM>_<servicing release>_<KB article number>_<build number optional>_<architecture identifier>

Extracted SQL Server file name schema

Once the primary SQL Server software update package has been downloaded and extracted, the file name will resemble the following:

<product name or component>-<KB article number>-<build number optional>-<version optional>-<architecture Identifier>-<language code optional>.exe

Extracted feature pack name schema

Once a software update package for a feature pack has been downloaded and extracted, the file name will resemble the following:

[feature pack file name].msi

• ProductName This is the full product name, which includes the product version information. For SQL Server, this attribute could be one of the following:
  • SQLServer2005
    SQL Server 2005
  • SQLServer2008
    SQL Server 2008
  • SQLServer2008R2
    SQL Server 2008 R2
• SP number or RTM The service pack level of the product or component which it can be applied on top of. RTM indicates the product without any service packs installed.
• KB article number The Microsoft Knowledge Base article number that is associated with the software update.
• Servicing Release The release type for the software update. For details, visit the “Package information and release types” section.
  • COD: Critical On Demand
  • OD: On Demand
  • CU: Cumulative Update followed by the cumulative update release number
• Architecture identifier This field is used to indicate on which processor architecture the particular hotfix package runs. The current options are the following:
  • x86: This package runs on x86 platforms.
  • ia64: This package runs on Itanium IA-64 platforms for 64-bit.
  • x64: This package only runs on AMD x64 and compatible systems.
• Version An optional field that indicates the version of the software release.
• Build number An optional field which is used to indicate the SQL Server build number included in the software update.
  For example, in SQL2000-KB840223-8.00.1007-ia64-ENU.exe, the build version of SQL Server is 8.00.1007. This will correspond to the file version of Sqlservr.exe and to the returned value from @@version run against this server instance.

Software update package and extracted file name mapping

The following tables illustrates the mapping between the “Download File Name” on the hotfix download page and the actual name of the package once downloaded and extracted.

SQL Server software Update Package

Package	Software Update Package Name	Extracted SQL Server File Name
CU Package for SQL Server 2005	SQLServer2005_SPx_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005-KBxxxxxxx-Arch-Lang.exe
CU Package for SQL Server 2008	SQLServer2008_RTM_CUxx_kbxxxxxx_10_00_xxxx_Arch SQLServer2008_SPx_CUxx_kbxxxxxx_10_00_xxxx_Arch	SQLServer2008- KBxxxxxxx-Arch.exe
CU Package for SQL Server 2008 R2	SQLServer2008R2_RTM_CUxx_kbxxxxxx_10_50_xxxx_Arch SQLServer2008R2_SPx_CUxx_kbxxxxxx_10_50_xxxx_Arch	SQLServer2008R2-KBxxxxxxx-Arch.exe
CU Package for SQL Server 2012 R2	SQLServer2012_RTM_CUxx_kbxxxxxx_11_00_xxxx_Arch SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQLServer2012-KBxxxxxxx-Arch.exe

SQL Server Feature Pack 

Package	Software Update Package Name	Extracted SQL Server File Name
SQL Native Client	2005_SPx_SNAC_CUxx_kbxxxxxx_9_00_xxxx_Arch 2008_RTM_SNAC_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SNAC_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SNAC_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SNAC_CUxx_kbxxxxxx_10_50_xxxx_Arch	sqlncli.msi
SQL Writer	2005_SPx_SQLWriter_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLWriter.msi
AS OLE DB for SQL Server 2005	2005_SPx_ASOLEDB_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_ASOLEDB9.msi
AS OLE DB for SQL Server 2008	2008_RTM_ASOLEDB_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_ASOLEDB_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_ASOLEDB_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_ASOLEDB_CUxx_kbxxxxxx_10_50_xxxx_Arch	SQLServer2008_ASOLEDB10.msi
AS OLE DB for SQL Server 2012	2012_RTM_ASOLEDB_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_ASOLEDB_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQL_AS_OLEDB.msi
ADMOMD.net	2005_SPx_ADMOMD_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_ADOMD.msi
XMO/SMO (Shared Management Objects) for SQL Server 2005	2005_SPx_XMO_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_XMO.msi
XMO/SMO (Shared Management Objects) for SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012	2008_RTM_SMO_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SMO_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SMO_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SMO_CUxx_kbxxxxxx_10_50_xxxx_Arch 2012_RTM_SMO_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_SMO_CUxx_kbxxxxxx_11_00_xxxx_Arch	SharedManagementObjects.msi
Reporting Services for SharePoint for SQL Server 2005	2005_SPx_RSShrPnt_CUxx_KBxxxxx_9_00_xxxx_arch	SharePointRS.msi
Reporting Services for SharePoint for SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012	2008_RTM_RSShrPnt_CUxx_KBxxxxx_10_00_xxxx_arch 2008_SPx_RSShrPnt_CUxx_KBxxxxx_10_00_xxxx_arch 2008R2_RTM_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch	rsSharePoint.msi (x86 and x64 only)
Reporting Services for SharePoint for SQL Server 2008 R2	2008R2_RTM_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch	rsSharePoint.msi (x64 only)
Report Builder Click Once	2008_RTM_RBClckOnc_CUxx_kbxxxxx_10_00_xxxx_Arch 2008_SPx_RBClckOnc_CUxx_kbxxxxx_10_00_xxxx_Arch	RB2ClickOnce.msi (x86 and x64 only)
Report Builder for SQL Server 2008	2008_RTM_RprtBlder_CUxx_KBxxxx_10_00_xxxx_Arch 2008_SPx_RprtBlder_CUxx_KBxxxx_10_00_xxxx_Arch	ReportBuilder.msi (x86 only)
Report Builder for SQL Server 2008 R2	2008R2_RTM_RprtBlder_CUxx_KBxxxx_10_50_xxxx_Arch 2008R2_SPx_RprtBlder_CUxx_KBxxxx_10_50_xxxx_Arch	ReportBuilder3.msi
Sap BI	2008_RTM_SapBI_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SapBI_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SapBI_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SapBI_CUxx_kbxxxxxx_10_50_xxxx_Arch 2012_RTM_SapBI_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_SapBI_CUxx_kbxxxxxx_11_00_xxxx_Arch	SapBI.msi
Stream Insight	2008R2_RTM_StrmNsght_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_StrmNsght_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_StrmNsght_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_StrmNsght_CUxx_KBxxxxx_11_00_xxxx_arch	StreamInsightClient.msi
Synchronization	2008R2_RTM_Synch_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_Synch_CUxx_KBxxxxx_10_50_xxxx_arch	Synchronization.msi
PowerPivot for Excel Client	2008R2_RTM_PPExcel_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_PPExcel_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_PPExcel_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_PPExcel_CUxx_KBxxxxx_11_00_xxxx_arch	PowerPivot_for_Excel_x86.msi
Stream Insight and Server	2008R2_RTM_PPServer_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_PPServer_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_PPServer_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_PPServer_CUxx_KBxxxxx_11_00_xxxx_arch	StreamInsight.msi
Master Data Services	2008R2_RTM_MDS_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_MDS_CUxx_KBxxxxx_10_50_xxxx_arch	MasterDataServices.msi (x64 only)
Data-Tier Application Framework	2012_RTM_DAC_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_DAC_CUxx_KBxxxxx_11_00_xxxx_arch	DACFramework.msi
ADOMD.NET	2012_RTM_ADMOMD_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_ADMOMD_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQL_AS_ADOMD.msi
LocalDB	2012_RTM_LocalDB_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_LocalDB_CUxx_KBxxxxx_11_00_xxxx_arch	SqlLocalDB.msi
Transact-SQL Language Service	2012_RTM_TSQLLAN_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_TSQLLAN_CUxx_KBxxxxx_11_00_xxxx_arch	TSqlLanguageService.msi

Best practices As a best practice, consider providing a name that you can use to easily identify packages during downloads.

Package descriptions

This section describes each of the packages that are listed and their purposes. Installing a newer MSI package over an older MSI package removes the older version in favor of the newer version. Uninstalling a feature pack update by using an MSI package will completely remove the component. However, for the main CU package, uninstalling the .exe file causes a rollback to the previously installed version.

SQL Server software update package

File name

SQLServer2005-KBxxxxxxx-Arch-Lang.exe (For SQL Server 2005)

SQLServer2008-KBxxxxxxx-Arch.exe (For SQL Server 2008)
SQLServer2008R2-kbxxxxxx-Arch (For SQL Server 2008 R2)
SQLServer2012-kbxxxxxx-Arch (For SQL Server 2012)

Purpose The SQL Server software update package will update the SQL Server instance by using a collection of all SQL Server hotfixes that have been created since the product was released. The package will apply updates to all installed components if an update has been made. This package will update SQL Server DB & Engine, Analysis Service, Integration Services, Reporting Services, Replication Engine, and Manageability.

SQL Server Native Client

File name

sqlncli.msi (For SQL Server 2005, 2008, 2008 R2, 2012)

Purpose Microsoft SQL Server Native Client is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server features. This installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Report builder

File name

ReportBuilder.msi (For SQL Server 2008)
ReportBuilder3.msi (For SQL Server 2008 R2)

Purpose Report Builder provides an intuitive report authoring environment for business and power users with a Microsoft Office look and feel. Report Builder supports the full capabilities of Report Definition Language (RDL) including flexible data layout, data visualizations, and richly formatted text features of SQL Server Reporting Services. The download provides a stand-alone installer for Report Builder.

Report Builder Click Once

File name

RB2ClickOnce.msi (For SQL Server 2008, SQL Server 2008 R2)

Purpose The Click Once version of Report Builder designed to be started from Report Manager or a SharePoint library.

Reporting Services for SharePoint

File name

SharePointRS.msi (For SQL Server 2005)

rsSharePoint.msi (For SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012)

Purpose Microsoft SQL Server Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 and 2008 report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.

SQL Writer

File name

SQLWriter.msi (For SQL Server 2005)

Purpose The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup. Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.

AS OLE DB

File name

SQLServer2005_ASOLEDB9.msi (For SQL Server 2005)

SQLServer2008_ASOLEDB10.msi (For SQL Server 2008 and SQL Server 2008 R2)

SQL_AS_OLEDB.msi (For SQL Server 2012)

Purpose The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining. Note: Microsoft SQL Server Analysis Services OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0.

XMO/SMO (Shared Management Objects)

File name

SQLServer2005_XMO.msi (For SQL Server 2005)

SharedManagementObjects.msi (For SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012)

Purpose The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005. Note: Microsoft SQL Server Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server Native Client.

ADMOMD.net

File name

SQLServer2005_ADOMD.msi (For SQL Server 2005)

Purpose ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.

SapBI

File name

SapBI.msi (For SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012)

Purpose The Microsoft Connector for SAP BI is a set of managed components for transferring data to or from an SAP NetWeaver BI version 7.0 System. The component is designed to be used with the Enterprise and Developer editions of SQL Server 2008 or 2008 R2 Integration Services. To install the component, run the platform-specific installer for x86, x64, or Itanium computers respectively. For more information see the Readme and the installation topic in the Help file.

Stream Insight (client)

File name

StreamInsightClient.msi (For SQL 2008 R2 and SQL Server 2012)

Purpose For current users of StreamInsight, a run time data aggregator. StreamInsight allows software developers to create innovative solutions in the domain of Complex Event Processing that satisfy these needs. It allows to monitor, mine, and develop insights from continuous unbounded data streams and correlate constantly changing events with rich payloads in near real time. Industry specific solution developers (ISVs) and developers of custom applications have the opportunity to innovate on and utilize proven, flexible, and familiar Microsoft technology and rely on existing development skills when using the StreamInsight platform.

Stream Insight (Server)

File name

StreamInsight.msi (For SQL Server 2008 R2 and SQL Server 2012)

Purpose StreamInsight allows software developers to create innovative solutions in the domain of Complex Event Processing that satisfy these needs. It allows to monitor, mine, and develop insights from continuous unbounded data streams and correlate constantly changing events with rich payloads in near real time. Industry specific solution developers (ISVs) and developers of custom applications have the opportunity to innovate on and utilize proven, flexible, and familiar Microsoft technology and rely on existing development skills when using the StreamInsight platform.

Synchronization

File name

Synchronization.msi (For SQL 2008 R2)

Purpose Microsoft Sync Framework is a comprehensive synchronization platform that enables collaboration and offline access for applications, services and devices. Using Microsoft Sync Framework runtime, developers can build sync ecosystems that integrate any application, with any data from any store using any protocol over any network. Sync Services for ADO.NET is a part of the Microsoft Sync Framework (MSF). Sync Services for ADO.NET enables synchronization between ADO.NET enabled databases. Because Sync Services for ADO.NET is part of the MSF, any database that uses Sync Services for ADO.NET can then also exchange information with other data sources that are supported by MSF, such as web services, file systems or custom data stores. For more information about the Microsoft Sync Framework, please go to Microsoft Sync Framework Developer Center.

PowerPivot Excel Client

File name

PowerPivot_for_Excel_x86.msi (For SQL Server 2008 R2 and SQL Server 2012)

Purpose Microsoft® PowerPivot for Microsoft® Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the software users already know and love — Microsoft® Excel. You can transform mass quantities of data with incredible speed into meaningful information to get the answers you need in seconds. You can effortlessly share your findings with others.

Master Data Services

Filename

MasterDataServices.msi (For SQL 2008 R2)

Purpose Master Data Services helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time.

Data-Tier Application Framework

Filename

DACFramework.msi (For SQL Server 2008 R2 and SQL Server 2012)

Purpose The SQL Server Data-tier Application (DAC) framework is a component that is based on the .NET Framework and that provides application lifecycle services for database development and management. Application lifecycle services include extract, build, deploy, upgrade, import, and export for data-tier applications in SQL Azure, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005 through SQL Server Data Tools and SQL Server Management Studio.

OLEDB Provider for DB2

Filename

DB2OLEDB.msi (For SQL Server 2008 R2)
DB2OLEDBV4.msi (For SQL Server 2012)

Purpose The Microsoft OLE DB Provider for DB2 v4.0 offers a set of technologies and tools for integrating vital data that is stored in IBM DB2 databases with new solutions. SQL Server developers and administrators can use the data provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor.

LocalDB

Filename

SqlLocalDB.msi (For SQL Server 2012)

Purpose New to the SQL Server Express family, LocalDB is a lightweight version of Express that has the same programmability features, but it runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites. Use this if you need a simple way to create and work with databases from code. Express can be bundled with Visual Studio, other Database Development tools or embedded with an application that needs local databases.

Transact-SQL Language Service

Filename

TSqlLanguageService.msi (For SQL Server 2012)

Purpose The SQL Server Transact-SQL Language Service is a component that is based on the .NET Framework. This component provides parsing validation and IntelliSense services for Transact-SQL for SQL Server 2012, for SQL Server 2008 R2, and for SQL Server 2008.

Frequently asked questions

1. I have SQL Server 2008. Do I need to apply both the SQL Server software update package and the SQL Server Native Client package on the server to get all SNAC fixes (e.g., consider linked server scenario where server is also a client)?
   If client and server are on the same machine, individually installing the SQL Server Native Client package is not required. If client and server are separate, apply the SQL Native Client package to the client, and apply the SQL Server Software Update Package to the server to get all updates.
2. I have SQL Server 2005. Do I need to apply both the SQL Server software update package and the SQL Server Native Client package on the server to get all SNAC fixes?
   Yes, both the SQL Server software update package and SQL Native Client Package are required to update the server and must be downloaded separately.
3. Do I need to install the Feature Pack and the SQL Server Software Update Package?
   Each KB article will clearly identify the packages which need to be applied to the computer to obtain the described fix. SNAC and other MSI’s (SQLWriter, XMO, RS Sharepoint, RB Clickonce etc.) are version bumped every CU even if there are no new fixes.
4. How will we adjust the KB’s once we reach 999999?
   
   Our KB’s are currently 6 digits but will be going to 7 soon. The schema shown above in this doc is using the Letter’s KB plus 5 X’s for a total of 7 characters to show our expected schema once we cross the 1,000,000 mark. Until such time do continue to use the given 6 digit KB.
5. How do I apply the SQLWriter.msi feature pack?
   At this time you will need to run the SQLWriter.msi after the CU or COD package that you have downloaded for you fix.

sp_spaceused SQl Function

 

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Syntax       

---

sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]

Arguments

---

[ @objname=] 'objname'

Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.

If objname is not specified, results are returned for the whole database.

objname is nvarchar(776), with a default of NULL.

[ @updateusage=] 'updateusage'

Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. updateusage is varchar(5), with a default of false.

Return Code Values

---

0 (success) or 1 (failure)

Result Sets

---

If objname is omitted, the following result sets are returned to provide current database size information.

Column name	Data type	Description
database_name	nvarchar(128)	Name of the current database.
database_size	varchar(18)	Size of the current database in megabytes. database_size includes both data and log files.
unallocated space	varchar(18)	Space in the database that has not been reserved for database objects.

Column name	Data type	Description
reserved	varchar(18)	Total amount of space allocated by objects in the database.
data	varchar(18)	Total amount of space used by data.
index_size	varchar(18)	Total amount of space used by indexes.
unused	varchar(18)	Total amount of space reserved for objects in the database, but not yet used.

If objname is specified, the following result set is returned for the specified object.

Column name	Data type	Description
name	nvarchar(128)	Name of the object for which space usage information was requested. The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats2 or sys.dm_db_index_physical_stats3 dynamic management views to obtain equivalent size information.
rows	char(11)	Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reserved	varchar(18)	Total amount of reserved space for objname.
data	varchar(18)	Total amount of space used by data in objname.
index_size	varchar(18)	Total amount of space used by indexes in objname.
unused	varchar(18)	Total amount of space reserved for objname but not yet used.

 

Remarks

---

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Note

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

Permissions

---

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

Examples

---

A. Displaying disk space information about a table

The following example reports disk space information for the Vendor table and its indexes.

USE AdventureWorks2012;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO

B. Displaying updated space information about a database

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned.

USE AdventureWorks008R2;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO