Wednesday, June 27, 2012

SQL Server-Dynamic Mgmt Views...

While working on Sql Server development projects, one of the tasks that is constantly performed is performance tuning of the queries. There are lot of tools/techniques available in sql server to perform this task. Since 2005 with the arrival of dynamic management views, the tracking of the queries that are being run on a sql server database has become a little more easier. One could use the DMV's to find out the totalcpu,totalduration and totalreads consumed by a query. The DMV's that can be used for the above purpose are:



In order to get the actual text and the query plan for a particular row present in sys.dm_exec_query_stats, one has to use the CROSS APPLY operator and pass a parameter to the sys.dm_exec_sql_text, sys.dm_exec_query_plan views. For example it would be something like:

select query_plan,
substring(stext.text, (stat.statement_start_offset/2)+1
, ((case stat.statement_end_offset when -1 then datalength(stext.text)
else stat.statement_end_offset
end - stat.statement_start_offset)/2) + 1) as txt
from sys.dm_exec_query_stats as stat
cross apply sys.dm_exec_sql_text(stat.sql_handle) as stext
cross apply sys.dm_exec_query_plan (stat.plan_handle) as qplan

In above code segment, the sql_handle value is passed as a parameter dm_exec_sql_text to get the actual sql text, and the plan_handle is passed to get the actual query plan. The query plan is displayed with a hyperlink, clicking on the link will show the execution plan of the query.

Tuesday, June 19, 2012

SSIS 2012-Data Viewer

As I am exploring SSIS 2012, one of the features which I have used in SSIS is the data viewer functionality. It is one of the ways to find out what data is passing through the data flow task. It gives an idea about the data that is being extracted from source before being committed into the destination table. This feature is useful to get an idea about the data like profiling the data that is being sourced. In SSIS 2008, in order to enable the data viewer, within the data flow task one had to right click on the flow connecting the source and destination, choose the Data viewers option and then choose Add from the Data flow path editor. In SSIS 2012 enabling data viewer has been made simpler. In the data flow task, right click on the path connecting the source and destination, then click on the Enable Data viewer option as illustrated below.

Once that is done, click the Edit option to edit the columns that need to displayed in the data viewer. In case the data viewer is not added one can disable the option by following the image shown below. Choose the Disable Data viewer option.

In Summary in this blog post I wanted to show case the data viewer option in SSIS 2012.

Monday, June 18, 2012

SQL Server Questions...

One of the most sought after topics is SQL Server Interview questions. All of us who are SQL Server Professionals would have gone through different interviews and would have had different experiences. No matter how senior the positions are in SQL Server there are certain concepts which would have to be understood for a person gaining an entry into SQL Server. Here is a article written on SQL Server central which gives out a set of sql server questions.
The questions in the above article addresses Database Administration area and Database Design. One of the questions goes like are the operations on a Table Variable Logged.

Thursday, June 14, 2012

SQL Server-Tuning

Recently I ran into an issue with a SSIS package. One of the Exceute Sql task was insert records into a table from a view on a sql server database. In the last few days the package started failing frequently and it was on the execute sql task inserting records, the error reported in the job logs was a network error. I started examining the source view and looked at the select statement of the view. I noticed somethings write way: The view was using other views, each view in turn were referencing 4-5 base tables,also there was one view which was used repeatedly in the joins. When i ran the Select statement from the view it took over 20 mins to bring the data back. I knew that nested views could be causing the slowdown, analysed the join and indexes. I took the view which was used again and again the joins, converted them into a CTE of its own. Once the CTE was created the data was inserted into a temp table. Once this was completed, the remainder of the view was converted into another CTE. Then i combined the temp table created earlier with the CTE and ran the query. The data was retrieved in less than 2 minutes which was a huge performance gain from earlier. One of the takeaways from this exercise was that when using a VIEW as a source, the underlying SQL of the view needs to be examined. In case there is nesting of views, there could be a different way to model the data that is needed. To summarize this was a very interesting exercise of performance tuning.

Wednesday, June 13, 2012

SSIS 2012-Project Deployment

In my previous blog post, i wrote about the different deployment models available in SSIS 2012. In continuation with the previous article, the Project deployment model uses Parameters. In fact with the Project deployment model Parameters become of paramount importance. Typically in Project deployment model parameters are used to assign package properties. A project usually contains packages and parameters and is built into a project deployment file (.ispac extension). As per MSDN/Books Online
A parameter specifies the data that will be used by a package. You can scope parameters to the package level or project level with package parameters and project parameters, respectively. Parameters can be used in expressions or tasks. When the project is deployed to the catalog, you can assign a literal value for each parameter or use the default value that was assigned at design time. In place of a literal value, you can also reference an environment variable. Environment variable values are resolved at the time of package execution.

At the center of the project deployment model is the project deployment file (.ispac extension). The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. The project deployment file does not capture all of the information contained in the Integration Services project file (.dtproj extension). For example, additional text files that you use for writing notes are not stored in the project deployment file and thus are not deployed to the catalog.

For more differences between Project Deployment and Package Deployment, please refer to the following link.

Tuesday, June 5, 2012

SSIS 2012 - Deployment Model

In SSIS 2012 there are two deployment models One is package Deployment Model and the new one being introduced is project deployment model. In SSIS 2008 we have been working with the package deployment model. In this model once a SSIS project is created, we have a project folder under which we have sub folders like Data Sources, Data Source Views and SSIS packages. In SSIS 2012 when we create a SSIS project it is by default created in the Project Deployment Model. In the Snapshot below we see Project Params, Connection Managers , SSIS packages and Miscellaneous.

This is where the new deployment Model has it s advantages: As one can see there is no data Source folder instead we have a Connection Manager folder. In this folder all the connections used by a package can be placed. This is done by : In the connection manager tab right click on a connection and choose the Convert to Project Connection.
Once this option is chosen the connection is added to the Connection Manager folder. Now the connections available in the connection manager folder are available for other SSIS packages with in the SSIS project. This becomes very useful since if a connection has to change it needs to be done to connection once within the Connection Manager folder, then the changes are visible to all the other packages that use connection within the project. In a nut shell Project Deployment options provides flexibility while working with many packages within a SSIS project.

Monday, June 4, 2012

SQL Server 2012 - New Functions...

With the release of SQL Server 2012, there are lot of new user defined functions which have been introduced in T-SQL. One of the popular or more used functions are CAST and CONVERT, these are used to perform conversion of data from one data type to another. There is a new function which has been introduced in SQL Server 2012 called PARSE. It has the following Syntax:

PARSE ( string_value AS data_type [ USING culture ] )
PARSE(), expects three parameters,
string_value – String value to parse into the specified data type.
data_type – Return data type, numeric or datetime type
culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.
SELECT PARSE(’23/04/2012′ AS datetime2 USING ‘en-GB’) AS [Using PARSE Function]

There are more functions which are described in detail in this article: