SQL Consolidation Considerations !!!

In modern times, with current focus of most organizations on saving costs by means of virtualization and consolidations., It becomes increasingly important for DBAs to ensure that help organization achieve this objective without compromising on the performance, scalability, availability & application compatibility.

In this blog post, I would like to give some pointers on the considerations to ensure predictable behavior from SQL Instance post consolidations

Application Compatibility

This is one of the most important and high impact considerations for the databases which you are planning for consolidation. For this we need to first take into account of the current Compatibility Level under Databases running. Each SQL Server instance can support only two previous Compatibility level of the databases. For e.g SQL 2012 supports only databases which are compatibility 90 & higher (90,100 & 110).

So if any of the databases are running under Compatibility Level 80 needs to be first upgraded and tested for latest database compatibility before considering it for consolidation.

In order to upgrade, we need to first perform a static TSQL Code Review to identify the incompatible or breaking or deprecated code. In addition to it, we need to use profiler deprecated features or Xevents to identify the deprecated TSQL code at run time. Further run Upgrade Advisor to ensure we are hitting any issues to be fixed before upgrade.

This is where majority of the Developers time will be spend and can delay the Consolidation Project.

In addition to this, it is important to identify any specific application dependency for e.g Jobs, Logins, Collation requirements, Linked Servers, access to file system, files etc. In short, identify any application dependency which lies outside the database.

Configuration Requirements

Each Application might have certain specific configuration requirements which might be conflicting with the requirements of other databases. For e.g Biztalk databases has mandate for MAXDOP 1 else we start seeing Deadlocks in the application. However MAXDOP 1 leads to serial execution of queries and hence might impact the performance of the other databases which were performing well with parallelism.

If there are conflicting requirements, you might consider splitting it into multiple instances of SQL Server on the same server. Further is it important to understand if there is specific Configuration requirement required from OS level or SQL level.

Storage Requirements

It is important to understand the Storage requirement for the database and amount of data added per growth for Capacity Planning. You can use MDW Disk Usage Reports to identify Data MBs added per day to estimate the storage required

IO Requirements

To gather the IO requirements, you can use perfmon counters on all the instance viz

Process (sqlservr)
IO Data Bytes/sec
IO Data Operations/sec
IO Read Bytes/sec
IO Data Read Operations/sec
IO Write Bytes/sec
IO Data Write Operations/sec
Physical Disk
Disk Bytes/sec
Disk Read Bytes/sec
Disk Write Bytes/sec

Once you gather the collective IO workload, you can present the data to the storage vendor who can estimate the number of Disks required to support the IOPs from all the database collective to ensure the Disk Latency is no more than 10-15 ms.

Memory Utilization

If you are looking for consolidation of few databases, you can use sys.dm_os_buffer_descriptors and sys.dm_exec_cached_plans to identify the memory footprint consumed by each databases in the current instance.

 

You can use the following query to identify the same

SELECT DB_NAME(database_id),count(page_id)*8/1024 As [DataPagesInMB] FROM sys.dm_os_buffer_descriptors
group by database_id

SELECT DB_NAME(dbid),SUM(size_in_bytes/1024/1024) As [ProcCacheInMB)
FROM sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
group by dbid

The sum of the memory will only give the Data/Index Pages and Object Cache requirements for the databases. But there are other memory components like connections, locks which are not accounted here. Those would be difficult to estimate and has to predicted.

However if you are planning to consolidate entire SQL Instance, You can use the total pages & Working Set, Private Bytes perfmon counter to estimate the memory requirements for the individual SQL Instance

Processor Utilization

The Processor Utilization data can be gather by using % Processor Time and Process (sqlservr) -> % Processor Time counters which gives the avg CPU requirements for the given sql instance.

If you want to identify the MAX CPU Utilization per database, you can use Resource Governor to limit all the user connections for those database to specific Workload Group and Workload Pool and then you can monitor the max cpu usage for the particular Workload Group.

High Availability and Disaster Recovery Requirements (HADR)

Another Considerations would be to understand the RPO and RTO for the databases which needs to be consolidated. Based on the Availability & DR requirements, you can consider Clustering, Database Mirroring or Log Shipping for individual databases.

With SQL 2012, you can also consider to enable indirect checkpoints based on RPO and RTO requirements.

SQL MAP Tool Assessment would serve as a good starting point for SQL Consolidation Projects since using SQL MAP Toolkit, one can get the Inventory of number of SQL Instance in a particular environment, current version, current OS Version, Avg CPU, Memory and IO utilization.

In addition you can use MDW, Resource Governor, DMVs, Upgrade Advisors, Xevents, Profiler for more detailed data capture and estimation.

The following Excel Sheet is the quick summary of the steps one need to perform for SQL Consolidation Projects

Consolidation

 

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

SQL Reporting Services Performance Tuning Tips & Tricks

It has been really long time since I have blogged. However I will try to blog as often as I can.

Very Recently, I performed a SSRS Performance Review to tune performance reports for one of our customers. While analyzing some long running reports we identified some usual issues which may not be aware while we are designing the report or working on small dataset. But when the same report is migrated to Production DW with large dataset & with number of concurrent users fetching the report, the response time of the report degrades.

In the following presentation, I have listed the common tips and tricks to performance tune the reports

 

 

Hope this helps !!!

 

Parikshit Savjani
Premier Field Engineer

All about Kerberos in Microsoft BI

Last week, I got an opportunity to present on BI SQLPASS Virtual Chapter on All about Kerberos in Microsoft BI. Kerberos Troubleshooting is one of the highest call volume generator for Microsoft CSS and hence I thought I would choose this topic to educate our Customers on steps to configure Kerberos for SQL & SQL BI products along with caveats.

It was 1 hr 30 min session and I had demos to configure Kerberos for SQL, SSAS, SSRS in native mode and SSRS 2012 in SharePoint Integrated Mode explaining why do we need Dummy SPNs starting SSRS 2012 in SP Integrated Mode. Unfortunately due to remote delivery and lag over Live meeting, I couldn’t cover all my demos and hence I will soon be planning to deliver a second part of the session which includes configuring Kerberos over SSRS Native Mode and SP Integrated Mode.

The Presentation was recorded and the recording will soon be available on BI SQLPASS Presentation Archive

I have uploaded the presentation deck on Slide Share and I am embedding the same below.

Finally I would like to Thank SQL BI PASS Virtual Chapter Team to provide me this stage for presenting to Global audience. It was a great experience and I would encourage the readers, to present in the SQLPASS VC and contribute to the community.

I will update on FB & Twitter when my second session is scheduled

Improving the Performance of the reports by simplifying Drilldown Report to Drill through Report

Recently one of my customers in IT industry, had a P&L drilldown report aggregating the P&L metrics from the company level->client level ->account level -> project level -> employees at the row level while aggregating the data across date hierarchy Calendar Year->Calendar Semester ->Calendar Quarter -> Month at the Column Group Level with Adjacent Column Group aggregating the data across Geography Hierarchy Country -> State ->City.

The report has number of expressions to derive few P&L metrics and each row group had a sort order defined as well.

The report consists of a single tablix which was fetching around 1-2 lacs of records depending various parameterized filters. The stored procedure responsible for fetching the data for the report runs within 50 sec, while the report was taking 30-50 mins to render on the report server. Yes you read it correct it took 30-50 mins to render and in this blog post I would like to explain why it was taking so long and how we improved the performance by simplifying the report.

To reproduce the scenario, I created a similar drilldown tablix report using AdventureworksDW2012 database with following Row Grouping and Column Grouping

 Row Groups

Product Category -> Product Sub Category ->   Color -> Size -> Product

 Column Groups

Calendar Year -> Calendar  Semester -> Calendar Quarter -> English Month Name

 Adjacent Column Group
Country -> State ->  City -> Reseller

  Measure

Order Quantity

In my report, I did not add any expression since we could still see the performance dip if this report. The report takes approx 15 sec to load as compared to 123 ms to execute to the query used to fetch the data for the report.

Now, as we all know, whenever we want to tune performance of the report, we need to first check the execution log to identify where the report is spending more time

SELECT * FROM dbo.ExecutionLog3
WHERE ItemPath like '%Drilldown%'
ORDER By TimeStart DESC

As seen in the above Execution Log, the report is spending most of it in processing. In order to improve of the report we need to tune the report processing time. The Report Processing time is mainly time spend in performing grouping, aggregation, sorting evaluating expressions. Also, report processing is a memory intensive operation and if the report server is installed on a memory constrained server, it is likely to get OOM errors or slowness of reports on that server when this report is being processed.

For small sets of data, such reports doesn’t consume much memory and renders within acceptable time for interactive viewing. However as the granularity of dimensions is lowered, the dataset size increases and thereby the processing time and memory requirements increases exponentially and when such reports are rendering in production environment with concurrent users, the performance degrades.

To improve and tune the processing of such kind of reports is to simplify the design of the report. For such kind of reports, it also very important to understand the requirement of the business user, who will be consuming this report. Since analyzing 1 lac records of data interactively from a single report view is non-realistic expectation.

Most Business Users would first like to see highly summarized data at Top level of the Hierarchy and if the Metrics needs further investigation, they might want to drill down to the Next level for a specific Member of Top level Hierarchy. So in case of the above report, the analyst is interested to view the data at the Product Category Level, and if he wants to investigate further on Bikes Product Category, he needs to expand the Bikes Category to list all the Sub Category listed under Bikes Category.

To meet this requirement of the business user as well as simplify the design of the report, we can break the above report in 3-4 report as mentioned below

  • The Main Report returning the Data aggregated at Product Category Group Only with a Drill Through Action defined on Product category column passing current selected Category field as a parameter to the second level drill through report.
  • The Sub Category Level Report with Category as one of the hidden parameters which helps to filter the Dataset to that specific Category and aggregating the Data at Sub Category Level Alone with a Drill Through Action defined on Product SubCategory column passing current selected Sub Category as a parameter to the third level drill through report.
  • The Color->Size->Product Level Drill Down Report with Sub Category as hidden parameter which filters the Dataset to that specific Sub category and thereby reduces the dataset to 30-40K records which is rendered by row grouping of Drill down in report in few seconds

 

 

With this approach, the following is the rendering time for 3 reports. As seen in the collection time for rendering all 3 reports is around 8-9 sec which is a good improvement as compared to single Drill down report

 

Thus, by simplifying the drill down report to 3 simple reports using Drill Through Actions we are able to meet the requirements of business users as well as improve the processing of our reports to acceptable time for interactive viewing. In case of my customer, we reduced the time for rendering the report from 30-50 min to 20 sec which was well appreciated

Hope this helps!!!

Parikshit Savjani
Premier Field Engineer

Unable to run DTS Package fetching data from Oracle using ODBC Driver after migrating to 64 bit

Recently one of my customers migrated and upgraded their servers from SQL 2000 32 bit  to SQL 2005 64 bit following which their DTS packages stopped working. In this blog post I intend to explain the troubleshooting approach, we adopted to resolve the issue along with the resolution to the problem.

When you migrate DTS packages to SQL 2005 or above version of SQL Server, we have 2 options to run DTS packages either by using DTSRun.exe or by using DTExec.exe apart from running it through DTS Designer which is used for development.

Here are few things to take into consideration when migrating to 64 bit server.

  • DTS Packages designed in 32 bit environment can only run with 32 bit run time and cannot be migrated to 64 bit.
  • 32 bit runtime are not installed by default in the installation of the SQL Server and should be downloaded from the separate Feature pack
  • In a 64bit SQL Server instance, SQL Agent is also 64 bit and hence it cannot execute 32 bit package directly using SSIS Job step type. So in order to execute the 32 bit package using 64 bit SQLAgent, you need to use Operating System CmdExec Job step which calls 32 bit DTSRun.exe or 32 bit Dtexec.exe
  • If your 32 bit DTS Package is run by using 32 bit DTSRun.exe/Dtexec.exe, and if the DTS Package fetches the data from Oracle, you need to install 32 bit version of the Oracle client and not the 64 bit Oracle client.

From the Oracle side, we need to check the following

  • The tnsping to the Oracle service should be working fine. If tnsping fails, you will have check and verify the tnsnames.ora,network,IP,port,firewall components and resolve that first before you proceed.
  • From the Server, use command line sqlplus to log into the source oracle server and ensure the user & password is authenticated fine.
  • In the default installation of Oracle client, SQLNet Authentication is set to NTS which means the authentication will be performed at windows level and if your SQLAgent account doesn’t have an access to the Oracle server, it will fail so in general whenever you are running DTS package which fetches data from remote Oracle server, you should turn off the Windows Authentication of Oracle and rely on the Oracle’s native authentication

To achieve this, you need modify the following parameters in SQLNet.ora

SQLNet.ora can be found in %ORACLE_HOME%\network\admin folder

SQLNET.AUTHENTICATION=(none)

SQLNET.AUTHENTICATION_SERVICES=(none)

Once we have the tested and verified above points, we should first run the DTS Package from the command prompt using DTSRun.exe or dtexec.exe and see if the package runs successfully. If you see any error here, it should be resolved first before scheduling  the package to run as Operating System (CmdExec) Job step in SQLAgent Job.

Also  by default the SQLAgent Job runs under the SQLAgent service account credentials unless proxy is configured. So ensure SQL Agent service account has all the necessary permissions to execute the DTS package, execute the Oracle binaries, load the Oracle dlls and load in the SQL Instance.

In our case, we were able to able to run the DTS Package using command line but when we schedule to run the DTS package as a Job, we received the following error in the Job History

[Microsoft] [ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed

The above error message is a generic ODBC provider error and searching it in the internet might result in many irrevelant hits.

However, since we had the package running fine under command prompt, we had an advantage in troubleshooting. So we decided to capture a procmon trace during a successful run from cmd and failed run from SQLAgent.

Successful RUN
———————–

  • Download and copy procmon on the server
  • Capture procmon on the server, filtering “Process Name” Is “DTSRun.exe” OR “Dtexec.exe”
  • Run the package from cmd prompt
  • Save the procmon trace file.

Failed RUN
—————–

  • Capture procmon on the server, filtering “Process Name” Is “DTSRun.exe” OR “Dtexec.exe” AND  “Process Name” Is “SQLAGENT90.exe”  OR “SQLAGENT.exe”
  • Run the SQLAgent Job which fails
  • Save the procmon trace file.

Successful RUN
———————-

Open the procmon from successful run and filter on “Path” contains “oracle” (this is because oracle home will have keyword oracle) and observe the files accessed from oracle client

 

 

Failed RUN

Open the procmon from unsuccessful and filter on “Path” contains “oracle” and observe the files accessed from Oracle client

 

 

As clearly observed in the failed run, the DTSRun.exe cannot find oci.dll and hence it cannot locate the oracle home which causes it to fail. However in the cmd prompt run, it finds the oci.dll in correct oracle home and proceeds with the successful execution.

Our first guess is, is the ORACLE_HOME environment variable correctly set, & is it set correctly in regedit path as well HKLM\Software\Oracle or HKLM\Software\Wow6432mode\Oracle

However it was set correctly in our case, so what is going wrong here. If you observe procmon run of the failed instance, you will see that DTSRun.exe looks for oci.dll in various path but cannot locate it in any of the paths while in successful run it can locate it under correct path. So the path variable might be different in both the cases.

So to verify our hypothesis, we ran the SQLAgent Job with CMdExec Job step but with the following command

Path > “c:\path.txt”

We checked path.txt and found that path variable for SQLAgent service account did not had the path for oracle home %ORACLE_HOME%\bin due to which it was unable to locate oci.dll which explains the failure

To resolve the issue, we had to set the path environment variable for the user profile which runs sql agent service as PATH=%PATH%;%ORACLE_HOME% and restart the SQL Agent service.

Following the restart, the Job began to execute the DTS Package and it run happily ever after :)

If you like my posts, please consider sharing it viz Twitter,facebook,linkedin etc

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

Gotcha – Listening SQL Server Availability Group Listener on non-default port

While configuring SQL Server AlwaysON Availability Groups, as a part of security hardening practice it is recommended  to configure Availability Group to listen on a non-default port other than 1433.

However the moment you configure your Availability Group Listener on a non-default port and if you SQL Server instance is also configured to listen on non default port, you might observe that you are unable to connect to the AlwaysON SQL Instance using the availability group network name and you might receive the following generic error

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

However when you try to connect to the Availability Group Listener by specifying network name and the port no. in the format

tcp:<ag listener name>,<port>

you are able to connect to the Availability Group SQL Instance.

So, the issue is you are able to connect by specifying the port no. but unable to connect with specifying the port no. Any experienced SQL expert, would tell you are unable to reach SQL Browser service which provides the port resolution for instances running on non-default port on the server.

This is an expected behavior by design of the SQL Client Provider ( yes, you read it correct it is not due to design of Availability Group Listener please read on to understand why?)

For the sake of comparison between SQL Server FCI and Availability Groups , one might argue the following

With SQL Server Failover Clustering Instance (FCI) as well, we have sql server virtual name and virtual IP which is also registered as clustered resource, but we never experience any error in connecting to the SQL Instance when running on non-default port.

Again, to compare Virtual IP & SQL Network Name in SQL FCI with Availability Group Listener, one needs to understand that the design of Availability Group Listener ( it is  more than just clustered resource )

In SQL FCI, the SQL Network Name resource and Virtual IP only maps the IP to the active node of the cluster owning the SQL Instance resource groups in the cluster whereas Availability Group Listener is an application code (similar to SQL Browser service) itself which accepts the connection and provides the read-only routing for reporting workloads based on the Application Intent parameter of the connection string.

I mentioned Availability Group listener is an application code, so does it run as a separate process or service ??

Nope, the listener application runs as part of the sqlservr.exe process which is very well explained by Arvind Shyamsundar in his blog post

http://blogs.msdn.com/b/arvindsh/archive/2012/09/12/alwayson-listener-connectivity-details.aspx

As Availability Group Listener is an application code which accepts the connection first to perform routing, it requires a separate port no. which is not required by SQL FCI Network Name and Virtual IP which merely maps the Virtual IP to active node owning the SQL resource group which is performed by cluster service. In case of SQL FCI, the connection is accepted directly by SQL Instance and hence there is only one port involved in SQL FCI  which is SQL Instance port.

Now that, we understand the functioning of Availability group listener, coming back to the main question where we started, why did the client give error when connecting to Availability Group Network Name

When you specify only the Availability Group network name as the connection string, the client provider assumes it as a connection to the default instance ( This is  because the named instance connection has ‘\’ in the connection string which is missing when u connect to the Availability Group Listener)  and hence takes your connection to port 1433.  However if neither Availability Group Listener nor SQL Instance runs on the default port of 1433, you might receive the error reported above.

( This is the reason why I said above, the behavior is due to the design of the sql client provider and not Availability group listener)

Hence the connection to the Availability Group Listener never reaches SQL Browser and always bypasses SQL Browser service since it is never considered as connection to the named instance

And why port 1433 since that is what is specified as the default port in the connection properties of the SQL Native Client if you check your SQL Configuration Manager

To conclude,

Consideration For running Availability Group Listener on non-default port:

When you are running the Availability Group Listener and SQL server instance on a non-default port, while making the client connection to the SQL Availability group listener you need to either specify the port no. in the connection string or use client side alias in order to successfully connect to the Availability Group Listener and leverage the read only routing. This is because connection to the Availability Group Listener never reaches SQL Browser and always bypasses SQL Browser service.

 

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

When do we need asymmetric storage with AlwaysON FCI and Availability Storage?

Before we start the discussion on SQL 2012 AlwaysON let me define the terminologies introduced with SQL 2012. As we all know with SQL 2012, we have AlwaysON solution which provides us with a HADR Solution.

SQL 2012 AlwaysON can be configured in 2 modes viz

  • AlwaysON      Failover Clustering Instance (FCI):  This is the traditional Failover Clustering Instance which we had with SQL Server since SQL 6.5. However the name is changed to AlwaysON FCI. There are lot of enhancements introduced in FCI as well which is can be read in this blog .
  •  AlwaysON Availability Groups: This is introduced in SQL 2012  which allows us to mirror a group of database to another instance which allows us to have upto 4 secondary some of which can be configured in Read-Intent mode thereby allowing to route the readable workload to secondary.

So with SQL 2012, we have the following High Availability (HA) and Disaster Recovery (DR) Solutions and I have drawn the following  comparison matrix which might help us to choose the best solution to setup HADR solution for your environment

You might have seen this matrix quite no. of time but I have specific intent of the matrix to explain the rest of the post.

Note: I am not including replication here as a HADR solution due to the complexity of replication architecture

AlwaysON FCI AlwaysON Availability Groups Database Mirroring Log Shipping
Provides High   Availability

X

X X
Provides   Disaster Recovery X X

X

Zero Data Loss   Possible (RPO)

X

X

X

Automatic   Failover

X

X

X

Performance Overhead X

X

Readable   Secondary

X

From the above matrix AlwaysON Availability Group is a clear winner here, since it meets all the criteria which one would need to for HADR solution and to utilize the secondary for read/reporting workloads.

However one of the major concerns with Synchronous AlwaysON Availability Group and an important one is the performance overhead it introduces due to synchronous commit on the remote secondary replica and the dependency of the performance on network. The only technique which doesn’t impact the performance of the sql server with high availability is AlwaysOn FCI but it doesn’t serve as a DR solution and doesn’t provide readable solution.

Now, if there is a requirement to have a HA solution without performance impact and a DR solution which provides readable secondary can be achieved by the combination of AlwaysON Faliover Clustering Instance (HA Solution) and Asynchronous AlwaysON Availability Group (DR Solution) with readable secondary.

Pictorial Representation

The above configuration gives us best of both the worlds, AlwaysON FCI serving a no performance overhead High Availability Solution while async readable secondary availability replica serves as a DR solution with readable secondary useful to offload reporting workloads.

However the configuration of AlwaysON FCI in conjuction with AlwaysON Availability Groups is not as simple as the above diagram looks. This is because to achieve above configuration we have the following considerations

  1. All the 3 nodes should be part of the same windows server failover cluster and should be part of the same domain. This is a mandate both for AlwaysON FCI and Always Availability groups.
  2. Node 1 and Node 2 are in the same site while Node 3 is at geographically dispersed site.
  3. Node 1 and Node 2 should have automatic failover while failover to Node 3 should be manual since it is an async secondary replica.
  4. AlwaysON FCI Group should be part of the same sql instance while AlwaysOn Availability Group requires a separate SQL instance.
  5. The shared storage between Node 1 and Node 2 shouldn’t be visible to Node 3 while the storage of Node 3 shouldn’t be visible to Node 1 and Node 2.

To summarize we need the following

  1. One Windows Server failover Cluster consisting of 3 nodes in same domain.
  2. Shared Storage between Node 1 and Node 2 while Node 3 requires a separate storage.
  3. Automatic failover between Node 1 and Node 2 but no automatic failover to Node 3.
  4.  Two SQL Server Instances.

Points 2 and 3 was something which was difficult to achieve with traditional windows server failover clustering. This is because with Traditional WSFC we could have only 2 types of configuration

Shared Storage visible to all the Nodes

                                                                                                                                                                                                                                                  Majority Node set or Geographical dispersed clustering

However, for our requirement we need the following setup. Shared Storage between Node 1 and Node 2 while separate storage for Node 3 –> Asymmetric storage

The above configuration is called Asymmetric Storage since the shared storage is partially visible to subset of the nodes. This setup was not possible with traditional clustering but starting Windows 2008 R2 SP1 and Windows 2008 (Hotfix) the asymmetric storage is supported by Windows Server Failover Clustering.

This support was introduced by Windows Team to support such kind of scenarios as required by SQL AlwaysON setup discussed above.

I hope after reading this post, you would have clarity on why the asymmetric storage concept is introduced by WSFC and when will it be required for AlwaysON configuration.

If you like my posts as much as I like writing it for you, please considering sharing it with others :)

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

Recovering from cryptic errors thrown when importing from PowerPivot

I am borrowing this title from one of the blog post from Cathy Dumas, since this post is just the extension of her blog post which she posted for resolving the error thrown while creating a SSAS tabular Model project in SSDT by importing from Powerpivot.

I started to create a SSAS Tabular Model project by importing the data model from Powerpivot sheet. As we know, for Tabular Model Project, a workspace database is created on the ssas tabular mode instance to facilitate the data load while importing the data in the project.

If you are not familiar with the configuration of the workspace database for SSAS Tabular Model Project, I would recommend you to read the following blog from Marco Russo

http://sqlblog.com/blogs/marco_russo/archive/2011/07/20/change-default-workspace-and-deployment-server-in-tabular-ssas-denali-ctp3.aspx
While creating a project by importing from Powerpivot, I received the following error message

Tabular Model Import Error

So while researching the error, I encountered Cathy’s blog post here. And I ensured to give read permissions  to the powerpivot file to the virtual service startup account  NT Service\MSOLAP$<Name instance>  of the SSAS instance which is hosting my workspace database.

However I continued to receive the same error. I also ensured that the user with which I have started the Visual Studio has permissions to read the powerpivot file and to create database in the SSAS Instance.

In my quest of finding a solution to the problem, I checked the msmdsrv.log file located in the C:\Program Files\Microsoft SQL Server\MSAS11.<Named Instance>\OLAP\Log folder.

In the Log file I saw the following error message

(9/8/2012 7:53:04 PM) Message: The file ‘\\?\c:\users\<username>\documents\visual studio 2010\Projects\<project name>\Model.abf’ could not be opened. Please check the file for permissions or see if other applications locked it. (Source: \\?\C:\Program Files\Microsoft SQL Server\MSAS11.<Named Instance>\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210023)

The above error message made me realize that creating the ssas tabular model project by importing from powerpivot extract the abf the file from the Excel sheet and places it in the Visual Studio 2010 Project folder in My Documents, however the service account NT Service\MSOLAP$<Named Instance> will definitely not have permissions to read/write in this folder.

So I explicitly gave read/write permissions to NT service\MSOLAP$<Named Instance> to my Visual Studio Project Folder in My Documents following which I was able to resolve the error message.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

Dependency Map of the Nested Stored Procedures using DMV

One of my customers, had a requirement to identify the call graph (or other words dependency map)  for the nested stored procedures. While with SQL 2008, two new DMVs viz  sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities  to identify the referencing and referenced objects by the stored procedures within the database.

However, the DMV gives the information of the immediate stored procedure referenced by the stored procedures, but if there is multiple level of nesting of stored procedure, it is not available out of box.

I have developed a following script which uses recursive CTE to develop dependency map of the nested stored procedure.

DECLARE @procname varchar(30)
SET @procname='dbo.Proc2'

;WITH CTE([DB],[OBJ],[SCH],[lvl])
AS
(
SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
referenced_schema_name AS [SCH],0 AS [lvl]
FROM sys.dm_sql_referenced_entities(@procname, 'OBJECT') 
INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
WHERE o.type IN ('P','FN','IF','TF')

UNION ALL

SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
referenced_schema_name AS [SCH],[lvl]+1 as [lvl]
FROM CTE as c CROSS APPLY
sys.dm_sql_referenced_entities(c.SCH+'.'+c.OBJ, 'OBJECT') as ref
INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
WHERE o.type IN ('P','FN','IF','TF') and ref.referenced_entity_name NOT IN (c.OBJ)  -- Exit Condition
)

SELECT * FROM CTE

--This is my test case where I tried it on 

/*
CREATE TABLE Table1 (a int, b int, c char(2));
GO
CREATE TABLE Table2(c1 int, c2 int)
GO

CREATE PROCEDURE dbo.Proc1 AS
    SELECT a, b, c FROM dbo.Table1;
    SELECT c1, c2 FROM dbo.Table2;
GO

CREATE PROC dbo.Proc5
AS
BEGIN
SELECT a, b, c FROM dbo.Table1;
END
GO

CREATE PROC dbo.Proc3
AS
BEGIN
SELECT c1, c2 FROM dbo.Table2;
EXEC Proc5
END
GO

CREATE PROC dbo.Proc4
AS
BEGIN
SELECT a, b, c FROM dbo.Table1;
EXEC dbo.Proc5
END
GO

CREATE PROC dbo.proc2 
AS
BEGIN
EXEC dbo.Proc1
EXEC dbo.Proc3
EXEC dbo.Proc4
END
GO

DROP PROCEDURE dbo.Proc1
DROP PROCEDURE dbo.Proc2
DROP PROCEDURE dbo.Proc3
DROP PROCEDURE dbo.Proc4
DROP PROCEDURE dbo.Proc5
DROP TABLE Table1
DROP TABLE Table2

*/

Hope it helps !!!

Parikshit Savjani
Premier Field Engineer

SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

The title for this post might sound ironical since rebuild of the index is used to remove the fragmentation of the index so how does rebuild of index affect the fragmentation of index? Well, this is what I would like to explain in this blog post.

Generally, for rebuilding of indexes (alter index … rebuild) optimizer tries to generate a serial plan with no parallelism, however for large tables, the index rebuild compels the optimizer to generate parallel plan to distribute the keys across multiple threads to perform parallel sorts so that indexes rebuild can be performed faster at the expense of higher CPU.

The Parallel Index rebuild Query Plan uses range partitioning where in each of the thread is given a range of keys which it sorts and build a smaller index subunits.Finally there is Gather stream operator which links all the index subunits together (logically) to form a logically contiguous index page links but physically non-contiguous.

Thus when the optimizer decides to go for parallel plan to rebuild the index, the parallel threads does extent allocation in noncontiguous allocation and thereby doesn’t eliminate the fragmentation completely and still leads to some fragmentation due to extent allocation in non-contiguous location.

Let me illustrate this with an example

create table T1
(
a int primary key,
b char(80)
)

--Performing large number of random inserts in the table which will cause fragmentation in the table

Declare @i int;
SET @i = 1
while (@i <=500000)
begin
insert into T1 values(@i,'Test')
insert into T1 values(1000000-@i,'Test')
set @i = @i + 1
end

--Let us know check the fragmentation in the clustered index of the table caused due to random inserts

select database_id,object_id,index_id,index_level,
avg_fragmentation_in_percent,fragment_count,
avg_fragment_size_in_pages,page_count 
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')


database_id object_id index_id index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
12                   197575742                  1              0        66.789458                                             2358                      1.4966                                      3529
12                    197575742                 1              1         90.9                                                           11                            1                                               11
12                    197575742                 1              2           0                                                               1                             1                                                 1

As we see in the above output, the leaf level of the clustered index (level 0) is around 67% fragmentated with 2358 fragments with around 1.5 pages per fragments. Hence the total clustered index takes around 3541 pages (3529+11+1).

Now let me rebuild the index by using the following command

alter index all on T1 rebuild WITH(MAXDOP=2)

Even if I do not specific MAXDOP=2 , SQL instance on my laptop will use parallelism of 2 by default since it is a dual-core machine.

After rebuild the index, let us check the fragmentation again.

select database_id,object_id,index_id,index_level,
avg_fragmentation_in_percent,fragment_count,
avg_fragment_size_in_pages,page_count 
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')

database_id object_id index_id index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
12                    197575742            1               0                        0.08688                                     8                                143.875                              1151
12                    197575742            1               1                         0                                                  2                                      1                                        2
12                    197575742            1               2                         0                                                  1                                      1                                         1

As expected we see in the above output, the fragmentation in the leaf level of the index is reduced to 0.09% with 8 fragments at leaf level and 2 fragments at the intermediate level. The leaf level has on an average 144 pages per fragments which intermediatel level has 1 page per fragments

Now let us truncate the table,populate the table again with the same script above and rebuild the index but this time WITH MAXDOP=1 setting and check the fragmentation of the index

TRUNCATE TABLE t1;
GO

Declare @i int;
SET @i = 1
while (@i <=50000)
begin
insert into T1 values(@i,'Test')
insert into T1 values(100000-@i,'Test')
set @i = @i + 1
end

alter index all on T1 rebuild WITH(MAXDOP=1)
GO

select database_id,object_id,index_id,index_level,
avg_fragmentation_in_percent,fragment_count,
avg_fragment_size_in_pages,page_count 
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('T1'),1,1,'DETAILED')

database_id object_id index_id index_level avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages page_count
12                   197575742       1          0                        0                                                        3                            383.333333333333                     1150
12                   197575742       1          1                         0                                                        1                                            2                                          2
12                   197575742       1          2                         0                                                        1                                            1                                           1

As we see in the output now, the leaf level of the clustered index has now only 3 fragments with each fragments having around 384 pages on an average per fragment. Further the entire leaf level now requires only 1150 pages as compared 1151 pages when the index is rebuild WITH MAXDOP=2. if we compare the intermediate level we now have only 1 fragments which consists of 2 pages as compared to 2 fragments observed previously

A fragment is a chunk or a segment in the datafile with continuous allocation of pages. So more number of fragments is an indication of more number of dis-continuous allocation of segments hence more fragmentation.

My laptop didn't had enough resources nor did I have patience for data load, but if you want to see difference more prominent, you can load more rows in the above script and if you have more cpus and hence more parallel threads used for rebuild, the amount of fragments will be huge

Using the above learning we conclude the following

Conclusion

For large databases/datawarehouse generally of the order of TBs which are hosted on the servers with large number of CPUs i.e SMP architecture, while rebuilding indexes it is recommended to rebuild with (MAXDOP=1). This can lead to index rebuild being slower but at the same time we ensure the indexes are completely defragmentated and further serial plan uses less resources (CPU and memory) as compared to parallel plan which causes high utilization of CPU and Memory.

Further, due to same reason in SQL 2008 R2 Fastrack Datawarehouse, Microsoft recommends to rebuild the index WITH (MAXDOP=1).

So, I would not recommend to use serial plan for rebuild indexes always. The answer is our favourite "Its depends", if the parallel index rebuild doesn't use much resources and reduces the fragmentation to a tolerable limit we can go for the parallel plan which is default. However if either the resource utilization caused by the index rebuild is high or if the fragmentation of the index is not reduced within the desired limit, we should go for MAXDOP=1

Further Reading
------------------

http://msdn.microsoft.com/en-us/library/ms191292(v=sql.90).aspx

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/12/13/index-build-strategy-in-sql-server-part-2-offline-parallel-no-partitioning-non-stats-plan-no-histogram.aspx

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2006/12/11/index-build-strategy-in-sql-server-part-2-offline-parallel-no-partitioning.aspx

Hope this helps!!!

Parikshit Savjani
Premier Field Engineer