Friday, April 29, 2011

Feature...

When a query is executed in sql server 2005 management studio, the results when show in grid format has the headers and results. When one wants to copy the results to excel, it does not copy the headers by default in sql 2005. In sql server 2008 there is options in the results which says copy the results with headers. In order to achieve the effect, there is an option in sql server 2005 management studio to copy results with the headers. Below is a screen shot: Once the option is set when the results are displayed in grid format, the data can be copied with headers to excel.













Monday, April 18, 2011

Performance Tuning-Continued...

As part of the Performance tuning exercise, one of the options that can be experimented with is the sp_configure 'max degree of parallelism' option. This options is an advanced option, in order to set the advanced options do the following:
sp_configure 'show advanced options', 1;

GO
RECONFIGURE WITH OVERRIDE;
GO
Once the above is done, run the following script:
sp_configure 'max degree of parallelism',0;

GO
RECONFIGURE WITH OVERRIDE;
GO
In the above script the value of max degree of parallelism is set to zero, which means that tell sql server to use all available processors. Any value other than zero can be set upto to the available number of processors.
Once can override the values of degree of parallelism by specifying the MAXDOP query hint in the query statement.

Wednesday, April 13, 2011

Performance Tuning...

One of the issues as a Sql server architect/developer faces constantly is performance issues of queries/applications on the SQL Server database. There are lot of ways to do performance tuning, here in this post I would like to highlight some of the steps in approaching performance issues. They performance can be seperated into two broad aspects, one would be the system/hardware resources aspect, the other would be the programming/query aspect. In other words the methodology would comprise of (1) checking/configuring system resources, and (2) tuning individual queries. Let us focus on the query/programming aspect. When optimizing a query, the following tools can be used: The options can be set before the actual query block.


1. SET STATISTICS IO ON
2. SET SHOWPLAN_XML ON
3. SET SHOWPLAN_TEXT ON
In the Manangement Studio, the Display Estimated Execution Plan and Include Actual Execution Plan can be used. While looking at the execution plans look out of table scans followed by clustered index scans and index scans. On interpreting the Statistics IO, look at the Logical reads, Physical reads parameters, also look at if there are any read-aheads.

The following Option can be used to check the index fragmentation on a table:
DBCC SHOWCONTIG('TestTable', 'TempIndx')

The following option can be used to Display the current distribution statistics for the specified target on the specified table.
DBCC SHOW_STATISTICS('TestTable','TempIndx')
UPDATE STATISTICS can be used to keep the statistics of the table up to date.

Some of the basic parameters that can be monitored on the System/Hardware side:
CPU Utilization. ( How much is CPU pressured, On an average when the CPU utilization starts getting over 60-70% range and starts following this pattern,one needs to pay attention to such instances)
Memory Utilization (How much of meroy is reserved for SQL Server).
Disk I/O and Network I/O rates.

In this post I have listed some basic steps towards Performance tuning, in future articles, I would attempt to post more detailed information on the performance tuning steps.





Thursday, April 7, 2011

Linked Servers...

For one of the projects I am working on right now, I had to create Linked servers in my development and QA environments. The requirement was that the remote servers were different in Dev and QA but the name of the linked server had to be the same name in Dev and QA environments. When one adds a linked server where the remote server is sql server, there are two options for server type. The first server type is SQL Server, the second option is Other data Source. In the first option one has to put in the remote server name( this is the actual sql server name), then click on Security in the Linked Server properties window. In the Security screen, one needs to enter the security context: this screen defines how one is going to login to the remote server. Once this done and you click OK, the linked server will be created, at the same time it also tests the connection to the remote server.
In the second option Other data Source, one has to choose the type of remote server ( sql server, oracle etc...) and then provide the Product Name,Data Source and Provider String. In this option one can give a user friendly name for the name of the linked server. This is the option i followed to kep the name of the linked servers  consistent across all the Dev and QA servers.