My First Page

My First Page

A computer is a machine that can be programmed to carry out sequences of arithmetic or logical operations automatically. Modern computers can perform ...

SQL SERVER – Cursor to Kill All Process in Database

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed.

———————————————————————-CREATE TABLE #TmpWho(spid INT, ecid INT, status VARCHAR(150), loginameVARCHAR(150),hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmdVARCHAR(150))INSERT INTO #TmpWhoEXEC sp_whoDECLARE @spid INT
DECLARE @tString VARCHAR(15)DECLARE @getspid CURSOR
SET @getspid =   CURSOR FOR
SELECT spidFROM #TmpWhoWHERE dbname = 'mydb'OPEN @getspidFETCH NEXT FROM @getspid INTO @spidWHILE @@FETCH_STATUS = 0BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))EXEC(@tString)FETCH NEXT FROM @getspid INTO @spidEND
CLOSE @getspidDEALLOCATE @getspidDROP TABLE #TmpWhoGO
———————————————————————–

SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SQL SERVER – ASCII to Decimal and Decimal to ASCII

ASCII – Returns the ASCII code value of the leftmost character of a character expression.

CHAR – Fixed-length non-Unicode character data with length of n bytes.

Examples:
----Decimal to ASCIISELECT CHAR(80)+CHAR(73)+CHAR(78)+CHAR(65)+CHAR(76) ASSQLAuthorityAuthor
GO
----ASCII to DecimalSELECT ASCII('P') AS SQLAuthorityAuthorUNION ALLSELECT ASCII('I')UNION ALLSELECT ASCII('N')UNION ALLSELECT ASCII('A')UNION ALLSELECT ASCII('L')GO







SQL SERVER – Script to Find Leap Year

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as a variable to @year but you can change it to any year which you want to check for leap year.

DECLARE @YEAR SMALLINTSET @YEAR=2000SELECT @YEAR AS YEAR,CASE
DAY(EOMONTH(DATEADD(DAY,31,DATEADD(YEAR,@YEAR-1900,0))))WHEN 29 THEN 'YES' ELSE 'NO'END AS LEAP_YEAR
GO

The result is

YEAR LEAP_YEAR
 ------ ---------
 2000 YES

The logic is from the given year create a Date which results for Feb 01 of that year. You can do this by adding 31 (DATEADD(YEAR,@YEAR-1900,0) results for Jan 01 of that year). Using EOMONTH finds the last day of the month. Using DAY function find out day value. If it is 29, it is a leap year otherwise it is non Leap year.

SQL Server- Improve SQL Server performance with a simple query

This is probably embarrassingly simple to a SQL professional, and it’s been around since SQL 2005, but I only discovered it today and it made a noticeable difference to my production databases so I’m going to share it just in case it’s new to anyone else.

The query

Run this query on SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database, based on the workload of the server since the SQL service last restarted. (The query just lists the indexes, it doesn’t create them!)

SELECT    'CREATE INDEX IX_Auto_' +

          CONVERT(varchar(max), MID.index_handle) +

          ' ON ' +

          [statement] + 

          ' (' +

          COALESCE(equality_columns + ', ' + inequality_columns, equality_columns, inequality_columns) +

          ')' +

          ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_statement,

          CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,

          [statement] AS table_name,

          equality_columns,

          inequality_columns,

          included_columns,

          last_user_seek,

          avg_total_user_cost,

          user_seeks,

          avg_user_impact

FROM      sys.dm_db_missing_index_details MID

JOIN      sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle

JOIN      sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle

ORDER BY  avg_total_user_cost * user_seeks * avg_user_impact DESC

The results

Running the query will return results of the following form, with indexes that have the largest potential improvement first.

Column	Description
create_statement	A SQL statement to create the proposed index.
potential_saving	The relative query cost saving of this index.
table_name	The fully qualified name of the table that the index applies to.
equality_columns	Comma-separated list of columns that contribute to equality predicates of the form table.column = constant_value.
inequality_columns	Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form table.column > constant_value.
included_columns	Comma-separated list of columns needed as covering columns for the query. These are columns that aren’t used as key columns for the index, but are included for quick retrieval by queries.
user_seeks	Number of seeks caused by user queries that the index could have been used for.
last_user_seek	Date and time of last seek caused by user queries that the index could have been used for.
avg_total_user_cost	Average cost of the user queries that could be reduced by the index.
avg_user_impact	Average percentage benefit that user queries could experience if this index was implemented. The value means that the query cost would on average drop by this percentage if this index was implemented.

How it works

Every time the SQL query optimiser analyses a query, it works out the best indexes for the filtering it will need to do. If these indexes don't exist, it nevertheless remembers that they would have been useful, including how much quicker the query would have been any how many times they would have been used. After the database has been up and running for a while, SQL Server will have a pretty good idea of which indexes would make a significant difference to your live workload.
The query above uses the Missing Index dynamic management objects to list all these indexes in order of potential query cost saving.

Caveats

• Obviously if you don't understand SQL indexes at all, it's best not to fiddle with them. While there is a very good chance that the top few suggested indexes will be beneficial, there's always a chance that you could seriously degrade write performance, or that you'll lock the table for a prolonged period while the index is created.
• Don’t create every index in the list! It’s better to create them one at a time and measure the improvements – after a while the savings will become more trivial and it’s more likely that the decrease in write performance will negate the query benefits.
• Not every possible index that might help will be returned. See Limitations of the Missing Indexes Feature.

Further reading

Before you dive in, I strongly recommend having a quick read through Finding Missing Indexes in SQL Server Books Online.