Hide UserName and Password with SQLCMD

SQLCMD is a great tool. We have certain scripts that uses sqlcmd .The best way to connect to SQL Server database using sqlcmd would be to use Windows Authentication (i.e.; with –E switch).Unfortunately Windows Authentication won’t work everywhere in enterprise when you have distributed databases running in different domain and under different RDBMS.So we had to use SQL Server Authentication and the username and password were hardcoded with sqlcmd.

One of the concern security team had was the username and password are hardcoded in the bat file which execute sqlcmd script and that should be avoided. Application normally saves their setting in registry and then gets the information from there. We were also looking for a way on how to do this with SQLCMD.

One of the article from MSDN which I found very helpful was

http://msdn.microsoft.com/en-us/library/ms188714.aspx

This had reference of setting environmental variables and then using it with SQLCMD.Specifcally the note was

If more than one type of variable has the same name, the variable with the highest precedence is used.

1.       System level environmental variables

2.       User level environmental variables

3.       Command shell (SET X=Y) set at command prompt before starting sqlcmd

4.       sqlcmd -v X=Y

5.       :Setvar X Y

SQLCMD gives variables which can be defined like SQLCMDUSER, SQLCMDPASSWORD, SQLCMDINI etc. The msdn  article has complete list of variables which can be set in environmental variables.

Lets start with connecting to SQL instance with SQLCMD.The screenshot shows the default connection which is Windows Authentication

Lets verify the username which sqlcmd uses to connect.The function SUSER_NAME() will return current user.

So SQLCMD has used windows authentication and  an inbuilt user account to connect to SQL Instance.

Now lets create a sql authenticated id "testsqlcmduser"  in SQL Server instance.

Please note I have selected  to enforce password policy though its not needed but still its a good practice to enforce password policy in sql authenticated id.

Now lets add this user  and its password in system environmental variable.

Go to Control Panel->System->Advanced setting and then click on Environment Variables.You would get a screen like this

You can add the variable in System Profile or current user profile.Click on new button and add the variable SQLCMDUSER and its value as shown below

Similarly add the SQLCMDPASSWORD variable

Click on OK and Apply button.The environmental variables ar all set.Now lets use it with SQLCMD.
Go to command prompt and type sqlcmd  and give the  SQL Instance Name.

As shown in screenshot above it will get connected .Now lets verify what is the userid which sqlcmd used to connect to SQL Server instance

It has used testsqlcmduser which is the sql authenticated id.How did it worked.We have not even passed the userid and password.Now when you miss -U and -P option sqlcmd checks the system and user profile variable to see if any of the sqlcmd variables are set.If so it uses that to connect to SQL instance.If there are none then it uses windows authentiction to connect to SQL Server instance.

Now lets execute some TSQL command with sqlcmd. I have created a small script  file with the following TSQL

use Encryption_Test;
select * from dbo.EncryptedCustomer;
select SUSER_NAME();

Basically it connect to a database Encryption_Test in my local sql instance which was created to test encryption in SQL Server 2008.The script then executes a select command on a table in that database which has some of the encrypted data.The last line returns the current connected user.

The result is shown below

Please note we have not specified  username or password.So we are hiding the details which are needed to connect to SQL Server instance.Even we can hide the SQL instance name by adding the value in variable SQLCMDSERVER.

Query timeout expired error in ASP application

Timeouts are common in any enterprise level application. With the huge size of database and concurrent users accessing application I am sure most of you might have encountered timeouts in application.

There can be many reasons for timeouts. The prominent ones are blocking or CPU/memory usage going high. Recently I had to work on application that was getting frequent timeout error.The error is shown here

             

On enquiring the details of application I was informed that it was an old ASP application and the database was running in SQL Server 2005.There is a particular report which when accessed throws the timeout error. A specific stored procedure was executed for this report and the parameter was date range supplied.

Like any DBA who has some knowledge on application programming and administration I thought of recommending them to increase the connection/command timeout value in connection string or IIS.But then a question flashed “Is this the best way to handle this issue?”.

The answer was No. Ideally I should monitor the instance for any blocking. If there are no blockings then check the CPU and RAM utilization. Execute a profiler and capture the statements utilizing high CPU and tune them and check if still you get timeouts or not. If still the issue persists then request network admin to start a netmon trace to check if data-packets across network are getting lost. 

On checking the instance for blocking (blocked processes) I couldn’t find anything. There was one more application on which I was closely working that also has the same issue of timeout but it  was predominantly due to processes blocking each other. But in this instance I couldn’t find any process that was blocked. Now the next doubt was CPU or memory usage.

To monitor the statements consuming high CPU I decided to start a trace using profiler. Please make sure that whenever working on application related timeout issue the event Attention in profiler is selected. This event would get fired whenever application gets a timeout. You can then check which TSQL statement was executed just prior to this timeout. The events selected in profiler were:

Attention

User Error Message

RPC: Starting

RPC: Completed

SP:Starting

SP:Completed

SP:StmtStarting

SP:StmtCompleted

SQL:BatchStarting

SQL:BatchCompleted 

On running the trace what I found was certainly the CPU time, reads and duration for the particular SP was high and I was able to locate the exact TSQL statement which was throwing timeout.

As shown in the screenshot the update statement was creating issue. The reads were high and it was consuming high CPU cycles. This update was executed inside a while loop and the same trace file  had details about the reads and CPU of previously completed update statements.

Now the next part was to check the execution plan of this update statement and create appropriate indexes. I was able to find a scan on a table that was having 4313995 records. Suggested them to create non clustered indexes on this table so that the table scan was replaced by a seek. Once the indexes were created the timeout errors were resolved. On checking the trace again what I found were the CPU time was drastically reduced.

This is one of the reasons for getting timeout errors. I am sure there are many other factors some of them I have explained before (blockings, network issue etc).Normally application developer thinks that these timeout are issues related to database but what I have seen is they are due to bad TSQL code or database/application design.

Nested Loop vs Hash Match

Last week I was called to help a team facing performance issue in one of the production database instance. This was the statement

Our application performance has good overall past 2 weeks, however today we notice that this one single SP is taking 50 secs to execute when the same took 2 secs yesterday .


The issue was with single stored procedure. The first thing to check was if there is any blocking in the database instance. No, nothing!!!. I couldn’t find any blocked processes in the instance.


Since the issue was with single stored procedure I started to perform the steps which every DBA will do.Took the list of tables on which this stored procedure is dependent on and checked their fragmentation details. No the tables were not fragmented.


OK then maybe there would be issue with statistics of these tables. Probably the statistics of tables were not updated. But when I checked the statistics were fine.


At this point I decided that a trace needs to be started to capture the execution plan of the queries. On  analyzing the execution plan I found that a particular select statement doing join on huge tables was using Nested Loop join. I remember the lines from a book written by Grant Fritchey that nested loop join are appropriate for small tables and for large tables a hash match join would be a better join.

Whoop is this the problem. Is optimizer creating a wrong plan? If so why. Do I need to give join hints here and see if optimizer generates a better plan? I was thinking about these all things then suddenly a though flashed across my mind. Why don’t flush the plan cache and see. I use the command
 DBCC FLUSHPROCINDB to flush all the procedure cache for the particular database .



Once the plan cache was flushed the stored procedure was behaving as expected. It was responding fast. Now I decided to capture trace with execution plan once again  to check if the nested loop join operator was changed or not. As expected the query was using hash match join and not nested loop.

Probably updating the statistics would have resolved the issue but the tables were huge and updating the statistics would have taken more than 20 minutes. I could have tagged the procedure cache to be recompiled by using sp_recompile stored procedure but at that time flushing the procedure cache looked a better option. The changed execution plan is given below.

   
Nested loop join works better if the tables joined are small.As long as you have small dataset being joined this is the best join that you would expect to see.

Hash Match joins are often very effecient on large datasets specially if one table is much smaller than the other.It works well  on tables which are not sorted on columns used in join condition.Be aware that sometimes it may also be an indication of a missing where clause or an of an index.

The KB article has the details on how the three join operators normally work
http://support.microsoft.com/kb/197297

Disabled jobs run automatically in SQL Server 2005/2008.

A common myth  is that there is a bug in SQL Server 2005/2008 that a disabled job will run automatically in  SQL Server unless its schedule is disabled. I was also thinking in the same way  till today this is when I decided to test the same.

The first step was to create a SQL Server Test job that executes sp_who2 in its step. The schedule of the job was run daily every minute. As usual job was running every minute.

Now decided to update the status of job in sysjobs  table in msdb.I disabled it using the query

update sysjobs set enabled=0 where name='Test_SQLBugJob'

The job was disabled as shown in the screenshot.

But to when I noted the job history what I found was the job was still executing.

The next thing to do was to update sysjobactivity table since it has a column next_scheduled_run_date so if I can make it null probably the job won’t execute.The query  given below was executed.

update sysjobactivity set next_scheduled_run_date=null where job_id=(select job_id from sysjobs where name='Test_SQLBugJob')

But still after the job was executing as per schedule. Though the next_scheduled_run_date was null but the job will continue running and  once its executed it will update the next_scheduled_run_date column with a new value.

Decided to give a try with SQL Server Management Studio. When disabled through SQL Server management studio things were good and the job never ran. Now what  commands does SQL Server management studio executes.

Started a profiler and captured the commands. Found that Management studio executes a command

sp_update_job.This stored proc in turn executes  another stored procedure sp_sqlagent_notify

which again executes an extended stored procedure xp_sqlagent_notify which is in SQLServer library file xpstar.dll.

SQLServer agent caches the data in memory.Unless  sp_aqlagent_notify is executed the cache is not refreshed.When you  use SSMS then the command gets executed and SQL Server Agent refreshes its cache will the new setting.

One of my friend  pointed out that the bug is logged in Microsoft lab

https://connect.microsoft.com/SQLServer/feedback/details/552707/job-disabled-and-schedule-enabled#details

But if the workaround is to restart the SQL Server agent which means its refreshing the cache.So the point here is if you want to disable a SQL Server agent job never execute plain TSQL update statement.Either you use SSMS or execute the stored proc sp_update_job.

 

SQL Server could not spawn FRunCM thread

Today got an annoying error with SQL Server 2008.The error when trying to start SQL Server service was

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

This error was logged in application log of Windows Event viewer.On checking the Widows system log the error was

The SQL Server (SEREENASQL2008) service terminated with service-specific error %%-2146885628.

Tried changing the startup account to LocalSystem account but it didn't help to fix the issue.

The error logged in both windows event viewer log also was not quite helpful.

At this stage I decided to check SQL Server error log which was in the path C:\Program Files\Microsoft SQL Server\MSSQL10.SEREENASQL2008\MSSQL\Log.

The SQL Server error log has an entry like

2011-02-18 22:57:43.43 spid15s     Clearing tempdb database.
2011-02-18 22:57:43.62 Server      Error: 26014, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      Unable to load user-specified certificate [Cert Hash(sha1) "BCF96EB3346FFC3580D3D833B7C39D1021D88152"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2011-02-18 22:57:43.62 Server      Error: 17182, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

Now this was interesting. I remember some two weeks back created a self signed certificate with IIS in my laptop and then installing it. The same certificate was used to enable SSL over IIS and SQL Server.Please note I have IIS and SQL Server in my laptop.I also remember deleting this certificate last week.So concluded that there is some issue with certificate generated by SQL Server.

From registry editor went to the path

HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MS$SQLINSTANCE_NAME\MSMSQLSERVER\SUPERSOCKETNETLIB

Deleted the value in Certificate key and then restarted the SQL Server service. Whoop it fixed the issue.

Read SQL Server Trace file using C# in VS 2010

DBAs love using the function fn_trace_gettable  to read  contents from a  trace file . Just pass the path of the trace file to this function and it will show the content of trace file.But what if developers want to read the same using .NET framework.You  can’t have the luxury of having SQL Server installed in every machine so that you can execute fn_trace_gettable. This function can’t be executed remotely using a linked server.

First thing what you need to do is open VS 2010 and give reference to SQL Server SMO  assembly Microsoft.SqlServer.ConnectionInfoExtended.dll and Microsoft.SqlServer.ConnectionInfo.dll

Once you have reference to these dll’s in your project then you can use  an object of  TraceFile class to read the content of TraceFile.

//Reads the trace file 

TraceFile trcFile = new TraceFile();
 
trcFile.InitializeAsReader(strFileName);

while (trcFile.Read())
{                   
     string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();

     string  strApplication=trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString();

}   

trcFile.Close();

But before reading these values you may need to check if these columns are null by using the code

trcFile.IsDBNull(trcFile.GetOrdinal("TextData"));

Once you have these values you can load it into a SQL Server remote database and then create reports based on these values.Please don’t forget to include Microsoft.SqlServer.Management.Trace namespace in your code.

What to do when you have performance issues in SQL Server

Till now as DBA one of the issue which I love the most to work or solve is performance issues in SQL Server.  I do enjoy when queries goes mad  and misbehave.Based on the production issues which I have seen in last 5 years as DBA I have listed some of the steps that you can follow when there is severe issues with performance of SQL Database server.

1) Check the hardware configuration (CPU, RAM).If the server is running on a low RAM or CPU.

2) Execute sp_who2   to see if there is blocked process in SQL Server. If there are blocking then please note the spid and execute DBCC INPUTBUFFER (spid) that will give you the SQL Statement that is executed. Kill the blocking process to resolve the issue. Always remember that killing a process can create data integrity issues in application if not handled with care.

3) Start a profiler to capture queries that are taking max duration to run.Please also note the column CPU and IO on profiler.A high CPU/IO consuming queries need to be looked on.

4) Once you have the query you need to find out why it’s taking long time. There are couples of reasons for that.

    Bad Execution Plan

    Statistics out of date

    Index heavily fragmented

5) Check the query plan of those queries for any table scan/clustered index scan etc. If possible create appropriate index to avoid the same.

6) Check the statistics of tables if they are out of date. If so execute Update Statistics command to update the statistics

7) Check the fragmentation of indexes. If tables are heavily fragmented then you may need to rebuild the same. If it’s a heap table then a clustered index might need to be created and then dropped.

8) Try to capture windows counters and sort if it’s a memory, CPU or disk issue. If it’s a memory issue and if you are running on a 32 bit system check if you can enable AWE to allocate more memory to SQL Server otherwise add more RAM. Remember SQL Server, Exchange server are crazy about RAM. They will try to have as much as you can supply.

9) Check if any of the scheduled jobs are running on the server.Backup jobs or other maintenance jobs will eat up CPU cycles.

10)If SQL Servers are running  in a  clustered environment then check if two or more instances are running on  the same node.If so please check if you can failover one to the other node.

These are some of the preliminary steps that you can carry out to find the root cause of issue.Depending on the issue you can involve an expert DBA to sort out the problem.

Finding the size of Index in SQL Server 2005/2008

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.What if you need to capture size of individual index on a table.Thats where the DMF sys.dm_db_index_physical_stats
comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes   and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database,ObjectId,Objectname,IndexId,IndexDescription,Size of Index in MB,Last Updated Statistics Date and Avg Fragmentation.

select DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,CONVERT(DECIMAL(16,1),(sum(avg_record_size_in_bytes * record_count)/ (1024.0 * 1024)))as [Size of Index(MB)]
,last_updated as [Statistic last Updated],Avg_Fragmentation_In_Percent
from ( SELECT Distinct DB_Name(Database_id) as'DatabaseName',Object_ID as ObjectId,Object_Name(Object_id) as ObjectName,Index_ID as IndexId,Index_Type_Desc as Index_Description,avg_record_size_in_bytes , record_count,STATS_DATE(object_id,index_id) as 'last_updated',Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) as 'Avg_Fragmentation_In_Percent'FROM sys.dm_db_index_physical_stats (db_id('PM_Db'), NULL, NULL, NULL, 'detailed')
Where Object_id is not null and Avg_Fragmentation_In_Percent <> 0) T
group by DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,last_updated,Avg_Fragmentation_In_Percent

Database Projects in VS 2010 - Deploy database with values in master tables

Recently went across a situation where I need to deploy a database in SQL Server 2008  using database projects of VS2010.Things were simple since when you build solution with  database projects in VS2010 it creates     db schema,db deploy  manifest file, db script file etc.When database project is deployed using VS 2010 it uses deployment API available in .NET.The link below describes how you can achieve your own solution

http://blogs.msdn.com/b/bahill/archive/2010/05/04/leveraging-the-visual-studio-2010-database-deployment-api.aspx

The same functionality can be achieved using sqlcmd.You can execute these sql scripts created using sqlcmd (sqlcmd gets installed when SQL Server 2005/2008 is installed).

I guess same results can be achieved with VSDBCMD also.Never tested that :-).

Now things looked challenging when I had to insert data in some tables  while deploying the database projects.In simple words if the database is deployed it should create database,database objects also it need to insert some data in master tables.
Thereafter I found out you can put all your insert statements in Post Deployment Script File.The database projects in VS 2010 will have a file Script.PostDeployment.sql , just put your insert or delete  statements in that file and when you deploy the project it will insert data in master tables.

 

Windows OS versioning and problem with @@version

Today I came across a problem in getting the OS name with @@version variable in SQL Server.The @@version  variable returns the build of NT engine(say Windows NT 5.2,6.0).If its 5.2 then the OS would be windows 2003 server, 6.0 indicates Vista.

Now if you are running on Windows7 or Windows2008 R2 then @@version will return Windows NT 6.1.
This is confusing since DBA can't make out whether the operating System is Windows 7 or Windows2008 R2.The reason is because Windows 2008 R2 uses the same engine of Windows 7.
The solution is you use command line utility systeminfo.exe which will display detailed configuration information about a computer and its operating system, including operating system configuration, security information, product ID, and hardware properties, such as RAM, disk space, and network cards.

If you are running on SQL Server 2005 or SQL Server 2008 then this utility can be executed using xp_cmdshell.
Please don't forget to enable xp_cmdshell  using sp_configure

Below attached is the sample script that will get you OS Name.

create table #tmpOSName
(Details varchar(8000))

insert into #tmpOSName(Details)

exec master..xp_cmdshell 'systeminfo'

select rtrim(ltrim(SUBSTRING(Details,9,LEN(Details)))) from #tmpOSName where Details like 'OS Name%'
drop table #tmpOSName



Please note this is dependent on xp_cmdshell ; so that needs to be enabled first.

SSIS job fails:The affinity mask and affinity I/O mask configuration should not conflict

I was called today to help an issue with maintenance plan in SQL Server 2005.The Integrity Job created with SSIS Package was failing. Neither the output file nor Job history had much detail except the error:

The package execution returned DTSER_FAILURE

To debug the issue I decided to capture trace with all events under Errors and Warning selected along with SQL:StmtStarting,SQL:StmtCompleted,SQL:BatchStarting,SQL:BatchCompleted ,RPC:Started
and RPC:Completed events selected.

After analyzing the trace file the error in which I was interested was Error: 5834, Severity: 16, State: 1 and  just below that it had entry

The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

On checking the configuration of the server it was found that  the same processor was affinitize for both SQL Server worker thread scheduling and for I/O processing.

Under this configuration when SQL Server executes RECONFIGURE statements it throws an exception.

I changed these values , set it separately ,restarted SQL Server (and of course SQL Agent)and tried executing  job .The integrity job succeeded without any error.These affinity value shouldn’t be shared for same processors or you should set  Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors option.