Today, i came across a cool ad-on for Lync. The LanguageTranslator. You can download it for free from http://www.conversationtranslator.com/.
Hope you like it :)
Thursday, 28 July 2011
THE MYTH BEHIND STORED PROCEDURE BEHAVIOUR
You are a trusted database developer of your company. You developed a Stored Procedure that will perform some important functions on the database. You tested the Stored Procedure and everything looks great. Stored Procedure runs the way you expected and within the anticipated timeframe. Everything looks great and now you are ready to deploy the code to the production environment. The production environment has same hardware as development/test environment therefore, you are confident that your procedure will run just fine.
Deployment day approaches and you/ops team deploys the code to the production environment. The Stored Procedure is live but it is not performing the way you had expected. You wonder what went wrong?
You may have experienced something similar where the stored procedure/query performs bad when deployed on the production SQL Server and the same stored procedure/query works perfectly fine in the test/development environment.
POSSIBLE CAUSE
It is possible for Stored Procedure/queries to behave differently in Dev/Test and Production environment if you have not cleaned up the query execution plan cache from the dev/test environment while performing tests. Stored Procedures or queries will first try to use an existing plan cache at every execution and in the above scenario that may very well be happening.
BACKGROUND
When any SQL statement is executed, SQL Server first looks through the procedure cache (by querying SYS.DM_EXEC_CACHED_PLANS) to verify that an existing execution plan for the SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the same SQL statement. If it cannot find an execution plan, it generates a new execution plan for that query.
BEST PRACTICE
The best practice for deploying the code in your production environment is to make sure you first test the code in the test / development environment by removing cached query plans so that you know how your stored procedure or queries would perform in "Cold" cache which is almost like reproducing the cache as though SQL Server had just been started.
CAUTION
DO NOT USE THE BELOW COMMANDS IN THE PRODUCTION ENVIRONMENT, AS YOU COULD BE FLUSHING OUT SEVERAL CACHED QUERY EXECUTION PLANS THAT MAY PERFECTLY BE WORKING FINE.
HOW TO:
Please use the below commands on the test/development environment to clear out the cached plans for queries/stored procedures.
CLEARNING A SPECIFIC QUERY CACHED PLAN:
EXECUTE THE QUERY YOU WOULD LIKE TO CLEAR THE CACHE FOR.
SELECT * FROM CUSTOMERS
Deployment day approaches and you/ops team deploys the code to the production environment. The Stored Procedure is live but it is not performing the way you had expected. You wonder what went wrong?
You may have experienced something similar where the stored procedure/query performs bad when deployed on the production SQL Server and the same stored procedure/query works perfectly fine in the test/development environment.
POSSIBLE CAUSE
It is possible for Stored Procedure/queries to behave differently in Dev/Test and Production environment if you have not cleaned up the query execution plan cache from the dev/test environment while performing tests. Stored Procedures or queries will first try to use an existing plan cache at every execution and in the above scenario that may very well be happening.
BACKGROUND
When any SQL statement is executed, SQL Server first looks through the procedure cache (by querying SYS.DM_EXEC_CACHED_PLANS) to verify that an existing execution plan for the SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the same SQL statement. If it cannot find an execution plan, it generates a new execution plan for that query.
BEST PRACTICE
The best practice for deploying the code in your production environment is to make sure you first test the code in the test / development environment by removing cached query plans so that you know how your stored procedure or queries would perform in "Cold" cache which is almost like reproducing the cache as though SQL Server had just been started.
CAUTION
DO NOT USE THE BELOW COMMANDS IN THE PRODUCTION ENVIRONMENT, AS YOU COULD BE FLUSHING OUT SEVERAL CACHED QUERY EXECUTION PLANS THAT MAY PERFECTLY BE WORKING FINE.
HOW TO:
Please use the below commands on the test/development environment to clear out the cached plans for queries/stored procedures.
CLEARNING A SPECIFIC QUERY CACHED PLAN:
EXECUTE THE QUERY YOU WOULD LIKE TO CLEAR THE CACHE FOR.
SELECT * FROM CUSTOMERS
OBTAIN THE PLAN_HANDLE FOR THE ABOVE QUERY
SELECT PLAN_HANDLE, ST.TEXT FROM SYS.DM_EXEC_CACHED_PLANS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST WHERE TEXT LIKE 'SELECT * FROM CUSTOMERS%'
GO
TAKE THE PLAN HANDLE FROM THE ABOVE QUERY AND CLEAR THE CACHE
DBCC FREEPROCCACHE ()
CLEARING ALL CACHED PLANS
CLEARNING THE CACHE AND LOGGING THE MESSAGE IN ERRORLOG
DBCC FREEPROCCACHE
CLEARNING THE CACHE WITHOUT LOGGING THE MESSAGE IN ERRORLOG
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
HOW TO QUERY ALL CACHED PLANS FROM LOCAL INSTANCE OF SQL SERVER?
SELECT PLAN_HANDLE, ST.TEXT FROM SYS.DM_EXEC_CACHED_PLANS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST WHERE TEXT LIKE 'SELECT * FROM CUSTOMERS%'
GO
TAKE THE PLAN HANDLE FROM THE ABOVE QUERY AND CLEAR THE CACHE
DBCC FREEPROCCACHE (
CLEARNING THE CACHE AND LOGGING THE MESSAGE IN ERRORLOG
DBCC FREEPROCCACHE
CLEARNING THE CACHE WITHOUT LOGGING THE MESSAGE IN ERRORLOG
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
HOW TO QUERY ALL CACHED PLANS FROM LOCAL INSTANCE OF SQL SERVER?
QUERY TO LIST ALL CACHED PLANS
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS
Courtesy: Saleem Hakani
SQLSERVER Black Box
Ever wanted to find out what was happening when SQL Server crashed? Your investigation should also include reviewing the SQL Server activities much like what profiler would do. SQL Server 2008 (Including SQL2K5) out of the box comes with a default trace enabled. This trace keeps track of configuration changes, process level information and other information that can be very helpful for troubleshooting SQL Server related issues.
The default trace file can be opened and examined by launching SQL Server Profiler and by loading the log.trc file from (\Program Files\Microsoft SQL Server\\MSSQL\Log\ ) location or by querying it with Transact-SQL using the fn_trace_gettable system function.
Alternatively, you can query the trace file using the following T-SQL statement:
SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc', default);
-- Make sure you point to the right drive instead of C:\ drive.
Note: By default this trace is on and can’t be stopped by using the SP_Trace_SetStatus system stored procedure. Instead, you will need to stop the default trace by using SP_Configure option. *It is not a good idea to stop this trace.
Some of the information provided by the default trace includes:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Queries by Average CPU time
Top Queries by Average IO,
Etc.
This data can also be accessed from the Admin reports (from SQL Server Management Studio right-click the registered server and select "Reports")
The default trace file can be opened and examined by launching SQL Server Profiler and by loading the log.trc file from (\Program Files\Microsoft SQL Server\
Alternatively, you can query the trace file using the following T-SQL statement:
SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc', default);
-- Make sure you point to the right drive instead of C:\ drive.
Note: By default this trace is on and can’t be stopped by using the SP_Trace_SetStatus system stored procedure. Instead, you will need to stop the default trace by using SP_Configure option. *It is not a good idea to stop this trace.
Some of the information provided by the default trace includes:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Queries by Average CPU time
Top Queries by Average IO,
Etc.
This data can also be accessed from the Admin reports (from SQL Server Management Studio right-click the registered server and select "Reports")
Courtesy: Saleem
Wednesday, 27 July 2011
How to change the SQL Server Instance name?
Scenario:
An year back I installed SQL Server 2008 instance (Default) on my machine (Name: HSG-PCS374322C). Two months back I renamed my machine to HSG-PCS-44975 .
Today, when I try to install MOM 2005 on my machine, I was stuck with the below error:
An year back I installed SQL Server 2008 instance (Default) on my machine (Name: HSG-PCS374322C). Two months back I renamed my machine to HSG-PCS-44975 .
Today, when I try to install MOM 2005 on my machine, I was stuck with the below error:
Click to enlarge
From the above error, I had to reinstall. Which is not feasible for me.
Either, I had to RENAME the Name of SQL Instance to match the Machine name or rename the machine name itself.
I would prefer to Rename the SQL Instance.
This is how I found out that the Instance is having the name as the Machine’s OLD NAME :
select @@SERVERNAME
HSG-PCS374322C
So in order to get the machine name and the SQL Server instance name back in sync I had do these steps:
1.Run this in Microsoft SQL Server Management Studio:
sp_dropserver 'HSG-PCS374322C'
go
sp_addserver 'HSG-PCS-44975' , 'local'
go
2.Restart SQL Server service.
From Command Prompt run below or you can just do this from Services.msc
net stop mssqlserver
net start mssqlserver
Now running select @@SERVERNAME gives me HSG-PCS-44975.
This is my very first post in this Blog. I know most of you are aware of this, but still this may help the Novice. Wish me good luck... :)
Subscribe to:
Comments (Atom)