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.

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.

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.

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.

Best Practices...

Recently we we had to move our sql servers from the business unit to the technology unit so that the sql servers could follow certain guidelines and best practices. When the move happened and I was ready to test sql scripts on the new sql servers which follow certain guidelines to handle security,user access and what is allowed/not allowed with respect to database objects. While I was performing the tests I ran into some errors and noticed that i was missing some scalar functions, on further inspection the functions were supposed to be available on the master database. I raised a flag here and noted that custom user defined functions and stored procedures had been created on the master database on the older sql server's. When database development  was done on these servers obviously certain guidelines and standards were not followed. Lack of adhering to standards ended up creating a clean up project which involved moving the custom user defined database/stored procedures from the master database to the user defined database. Based on what i have read/listened to in sql server classes, one should avoid creating custom user defined functions and stored procedures on the system databases of sql server such as master,msdb and tempdb. It is always a good idea to follow/enforce certain guidelines while working on database development projects.

Mobile BI...

The paradigm of computing keeps changing, in the last couple of years there were new platform which have made in roads and are in process of becoming firmly established. Platforms like cloud computing, mobile frameworks such as smartphones,tablets are continuing to grow, given this perspective it seems like BI is moving into mobile space. Microstrategy has come out in a big way by making BI available on the ipad at the same time direction from Microsoft in this space is not very clear. It is kind of interesting because Microsoft is now pushing windows phone 7. There are other vendors who have come out with BI offerings for the mobile platform. Here are a couple of vendors which i have researching and going through the offerings.
RoamBI: http://www.roambi.com/, the second vendor i am looking at is: http://www.pushbi.com/. I am looking to attend some free webcasts to get an idea of the products. I also questions in mind as to how much value can BI on mobile platform can offer to the Business user.

SQL Server (Denali)-CTP1

I downloaded the SQL Server Denali CTP1 version fro MSDN and began the installation process. The first thing i came across was that my OS was not up to the required level. I found out based on the requirements that if one has vista then SP2 needs to be applied. Once the SP2 was accomplished went through the setup process. It was very similar to the setup of sql server 2008 R2 version. On completion of the setup launched the Management studio, in the splash screen it displayed Powered by Visual Studio at the right hand corner. It gave an indication that the UI of SSMS is going to be more tied in with visual studio 2010. I have just begun to play around with queries in Denali,one of features that has been enhanced is Intellisense, there are more options in the Intellisense menu. I will planning to provide more updates as i explore the features of Denali. I also installed the BIDS, Integration services and reporting services components of denali.