Longest Running Procedures in Sql Server

Its useful to determine which SProc/Code have the greatest impact on the Server. Sometimes, that is determined by examining the I/O cost,sometimes by the Exectution Duration. In this eg, Total Impact is determined by examining the length of execution and the frequency of execution.

SELECT TOP 10 
 temp.text as ProcedureName, 
 s.execution_count as ExecutionCount, 
 isnull( s.total_elapsed_time / s.execution_count, 0 ) as AvgExecutionTime, 
 s.total_worker_time / s.execution_count as AvgWorkerTime, 
 s.total_worker_time as TotalWorkerTime, 
 s.max_logical_reads as MaxLogicalReads, 
 s.max_logical_writes as MaxLogicalWrites, 
 s.creation_time as CreationDateTime, 
 s.total_physical_reads as PhysicalReads,
 isnull( s.execution_count / datediff( second, s.creation_time,getdate()), 0 )as CallsPerSecond
FROM sys.dm_exec_query_stats s 
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) temp 
ORDER BY 
-- s.total_elapsed_time DESC 
s.execution_count desc

Index to be recreated in Sql Server

When your database grows, the index fragmentation becomes too high,that will scale down the performance of the sql server. To overcome that we need to re-build the index, that should be done at the down time of the server.Here there is a query that will list all the index to be recreated/rebuild.It uses the following tables(sys.indexes,sys.tables,sys.schemas,sys.dm_db_index_physical_stats,sys.partitions). For detailed checkup change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED'

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name +'] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD'
ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' +
cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id,avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT
partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

Sql Server Hidden Features

Stored Procedures

* sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
* sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
* sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
* sp_helptext: If you want the code of a stored procedure
* sp_tables: return a list of all tables
* sp_stored_procedures: return a list of all stored procedures
* xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
* xp_fixeddrives:: Find the fixed drive with largest free space
* sp_help: If you want to know the table structure, indexes and constraints of a table
Functions

* HashBytes()
* EncryptByKey
* PIVOT command

TableDiff.exe

* Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables.
See More

Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff

Identity Coloumn Insert

Last week i need a scenario to insert values into a table having an identity coloumn only.

eg: create table test(id int identity)

Is it possible to insert values into the without setting identity insert off/on.I find out a method as follows.

insert into test() default values
go 20

This will insert 20 records to the table haaa...
Will update later..

Top Ten IIS Performance Optimization

1) Enable HTTP Keep-Alive
This setting is enabled by default. Set a side that this could improve client connection experience, this must be enabled in order for integrated authentication or all connection based authentication to work.

2) Adjust Connection Timeouts
Right on the spot, as highlighted by the author. You may want to adjust this according to your needs. To me, I felt 120 seconds is way to long for a connection timeout. Typically, I set for 30 to 45 seconds, if "I', the IIS server take longer than that to response to client or waiting for data, I would just drop it. No point holding the resource for more than 30 seconds :) Obviously, you need to evaluate your environment to derive the correct timeout value.

3) Enable HTTP Compression
Yes, some prefer third party tool like httpzip or xcompress. While if you like me. I will stick with the built-in compression feature. Refer this kb to add in more document types for compression:
Click How to specify additional document types for HTTP compression

4) Grow a Web Garden
This can be tricky, although this will boots up your application response time, do take note that if you are using any session variables make sure you have out-of-process session management in place, otherwise - since all worker processes inside the web garden do not share the memory space, having an in-process session management will cause session detail lost if the request was previously handle by other worker process. The following KB explains about the session management for ASP.NET.
Click
INFO: ASP.NET State Management Overview

5) Adjust the IIS Object Cache TTL
Great suggestion, the default value is too fast. Typically, I would go for 60 seconds at least. Unless, your site is pure dynamic and content must not be cached. In related to this Object TTL, You would also have the UserToken TTL, by default this is 15mins, which I think suitable for most of the setup. If you have a pretty dynamic user accounts management. E.g. temp account created on the fly and only valid for a short period, you might want to shorter the value. With IIS 6, setting it to '0' will disable the UserToken cache. More info, refer
Click
Changing the Default Interval for User Tokens in IIS

6) Recycle
Specifically, apply to IIS 6 only. While most users will stick the default, I on the other hand will disable all recycling event coz I think if an application is well-written and tested. It should not behavior weird and causing issue :) Well, this is just my wish, in real life - especially those IIS that you manage? For sure haunting you from time to time. I remembered in the past where developer always complaint about “something wrong with your IIS configuration’, while you, the system admin on the other hand keep fighting back saying that you are innocent, it is the code that causing this. Until, you find way to prove that it has nothing to do with IIS and the problem is with the application, your boss would typically go with developer :) Anyway - back to the topic. If you are seeing problem with your application, you should use any of the recycle events to keep up the application availability until you figure out what's wrong. Also, always checked 'shutdown worker process after being idle for' for X minutes. Recycling is good, not only it refreshes your application (sometime I don't agreed with this view), but it also returns the unused resource by to the system pool. Oh ya! What do you do with IIS 5? Well, you can try the IIS5 process recycling tool, refer
Click
How to recycle IIS 5.0 with the IIS 5.0 Process Recycling tool

7) Limit Queue Length
I'm happy with the default limit, there's typo in the article. Default is 4000 not 1000 :) And of coz at any time - you should not see lot of queue requests, if you do see that, meaning you are either experiencing hardware bottleneck or something really wrong with your application.

8) Shift Priority to the Working Set
I am lazy :) I never really change this setting unless is for SQL server. When you running on a low power box with less cpu power and memory - you should look into this.

9) Add Memory
Mm.... to my standard - I have lot of budget - my web server are typically equipped with 2GB memory and this is good enough for most of the web application out there. Of coz, depending on the number of users and application nature - you may need more ram or even setup network load balancing. In the past, I have been seeing to boots up web server you need more CPU processing power, while database server needs tons of memory. That's just what I have experienced, anyway - for you to make the right call - always do a performance monitoring to determine if memory is a bottleneck.

10) Use Disk Striping
This is like a bonus. In my opinion, since you have no control for the IIS binaries reside on the system partition, you should have maximum read output for your website pages, and maximum write performance for the log files. Hence, you should have a mirrorset for web pages, and disk striping without parity for the log files. Also, it always the best practice to have the above (IIS binaries, Web pages, IIS log files) in three different partitions/drive and secure it properly with NTFS permissions.

Enabling HTTP Compression in IIS 6.0

HTTP Compression is when a Web server receives a request for a file and instead of just serving the file to the client, it checks to see if the client browser (or application) is "Compression Enabled". If so, the Web server does a check on what type of file is being requested (this is determined by the file’s extension). If the file is marked as a static file, such as an HTML file, IIS will check the Compression Cache Temporary Directory.

Tip: To find the Compression Cache Temporary Directory, open up IIS and right-click on the Web Sites node and go to the Service tab. There is a text box that has a label next to it marked Temporary Directory, although it may not yet be enabled.

If a compressed version isn’t found, IIS will send an uncompressed version of the file to the client and a compressed version is placed in the temporary directory (IIS will only serve to the client from the temp directory). If the compressed version is found, IIS will send the file directly to the requesting client. If the requested file is a dynamic file, such as an ASP.NET Web form, then the response is dynamically compressed and sent to the requesting client (no temp directory access is ever done).

Enabling HTTP Compression on your Windows 2003 Server

There are quite a few steps to enabling HTTP Compression on your server. If you follow the steps in this article, you shouldn’t have any issues.

First, open up IIS and right-click on the Web Sites node and go to Properties. Click on the Service tab. As shown in FIGURE 1, you’ll see two options: Isolation mode and HTTP compression. If the Run WWW service in IIS 5.0 isolation mode check box is checked, IIS will run almost exactly like IIS 5.0. This means you won’t be able to take advantage of things such as Application Pools, which in my opinion are worth the upgrade to IIS 6.0 by themselves.



FIGURE 1: The Web Sites Properties dialog box

We’ll utilize the options within HTTP compression in this article:

Compress application files. Check this to compress application files. If you do select this, you must also have Compress static files checked, although you won't be warned of this need.
Compress static files. Check this to compress static files. After you do so, the Temporary directory text box is active.
Temporary directory. You can leave this at the default, which is %windir%\IIS Temporary Compressed Files, or set it to a custom folder. This is where temporary compressed static files will be stored.
Maximum temporary directory size. This option enables you to set the maximum size of the temporary directory. After the size is met, items are removed based on duration; the older files are removed and the new files are put in.
Next, go to the Web Service Extensions node. Right-click in the right pane, and click Add a new Web service extension. The New Web Service Extension dialog box appears, as shown in FIGURE 2. You can enter any name for the extension, but what others, including myself, recommend is HTTP Compression.



FIGURE 2: The Web Service Extension dialog box

Click on Add. Choose C:\WINDOWS\system32\inetsrv\gzip.dll (your path may be different, but that is doubtful), and click OK. Check the Set extension status to Allowed check box, and click OK.

IIS 6.0 Metabase Configuration - MetaBase.xml
Open up Windows Explorer and go to C:\Windows\System32\inetsrv. Find MetaBase.xml and make a copy (you can just highlight it and do a Ctrl-C, then a Ctrl-P to make a copy of MetaBase.xml). Now open up MetaBase.xml in a text editor. Find the section. Be careful, there are two sections here: one for deflate and one for gzip. We want gzip so the Location attribute of the element will have the following value:

Location ="/LM/W3SVC/Filters/Compression/gzip"

Look for the HcScriptFileExtensions section. Your default should have: asp, dll, and exe. This is where you add any extensions you want to compress for dynamic files. In my case, I added aspx.

You’ll notice many other attributes in FIGURE 3. These are the ones I find most important:

HcDoDynamicCompression. Specifies whether dynamic content should be compressed. This is important because dynamic content is by definition always changing, and IIS does not cache compressed versions of dynamic output. Thus, if dynamic compression is enabled, each request for dynamic content causes the content to be compressed. Dynamic compression consumes considerable CPU time and memory resources, and should only be used on servers that have slow network connections, but CPU time to spare.
HcDoStaticCompression. Specifies whether static content should be compressed.
HcDoOnDemandCompression: Specifies whether static files, such as .htm and .txt files, are compressed if a compressed version of the file does not exist. If set to True and a file doesn't exist, the user will be sent an uncompressed file while a background thread creates a compressed version for the next request.
HcDynamicCompressionLevel. VAL(1-10) specifies the compression level for the compression scheme, when the scheme is compressing dynamic content. Low compression levels produce slightly larger compressed files, but with lower overall impact on CPU and memory resources. Higher compression levels generally result in smaller compressed files, but with higher CPU and memory usage.
HcFileExtensions. Indicates which file name extensions are supported by the compression scheme. Only static files with the specified file extensions are compressed by IIS. If this setting is empty, no static files are compressed.
HcScriptFileExtensions. Indicates which file name extensions are supported by the compression scheme. The output from dynamic files with the file extensions specified in this property are compressed by IIS.




FIGURE 3: Essential attributes

For this example, just add aspx to the HcScriptFileExtensions section as I did, but don’t try to save. It won’t work because the file is locked by default if IIS is running.

Tip: To change this default behavior, open IIS and right-click on the top node, Internet Information Services, and check Enable Direct Metabase Edit.

Warning: The help documents state that you should use a space delimited list for the file extensions, and I have found this to be incorrect. Instead, use new lines and tabs like the following:

HcCompressionDll="%windir%\system32\inetsrv\gzip.dll"
HcCreateFlags="1"
HcDoDynamicCompression="TRUE"
HcDoOnDemandCompression="TRUE"
HcDoStaticCompression="TRUE"
HcDynamicCompressionLevel="10"
HcFileExtensions="htm
html
txt"
HcOnDemandCompLevel="10"
HcPriority="1"
HcScriptFileExtensions="asp
dll
exe
aspx">


The final step is to do an IIS shutdown and restart by right-clicking in Internet Information Services node and then click All Tasks, Restart IIS.