Finding Longest running query in SQL Server/MSSQL

In MySQL we use 'show full processlist' command to find if there is any query running for long time. This has helped me in some MySQL setups and I am able to find the problematic MySQL queries which is affecting the application performance.

However, in MSSQL this has been challenge as I am not able to find any straight forward solution in the SQL studio/Query.

Of late, for my MSSQL related queries I look up to one blog where I find almost all answers to my MSSQL problems. That is http://blog.sqlauthority.com/ .

This is been a excellent source of information for me and I hope all others who visited his blog might feel the same as well. Thanks Pinal Dave.

I have pasted his query which helped me to find the QUERY WHICH IS RUNNING for LONG time.

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO

More info on the following links of his blog.

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
http://blog.sqlauthority.com/2009/01/23/sql-server-2008-2005-find-longest-running-query-tsql-part-2/

Cheers !!
Krishna.

wget command syntax to access URL via Proxy

Folks, this is been a long time since I have posted here. Hmm ...lot of work :)

Use case :

Last week I faced a situation where I am not able to access an External URL ( meaning the setup is behind the Proxy ) via proxy from the java code.

However we were able to access the same URL using browser. It was real tricky that as we were not able to pinpoint the exact error which is causing the problem while accessing via JAVA code.

Finding the Error :

Used 'wget' tool, to access the URL which gave the exact error.  Given below the syntax of the same using Proxy .

Syntax :

wget -e http_proxy=http://<proxyhost>:<proxyport> --proxy-user=<proxyusername> --proxy-password=<proxypassword> <URLName>

Please note the argument '-e'  which is required if you need to specify the 'http_proxy' argument and this 'http_proxy' will not be available in the help page of wget.



Cheers !!
Krishna.

Finding MSSQL is 32 bit version or 64 bit version

Folks,

As like my previous post, I have to find whether the MSSQL server installed in a machine is a 32bit version or 64 bit version. This is possible with a simple following query .

select SERVERPROPERTY(‘edition’)

The output will show the Edition name alone for 32 bit. For 64 bit, the Edition name have 64-bit string appended to it.

Sample output :

For 32 bit : Enterprise Edition

For 64 bit : Enterprise Edition(64-bit)

Finding MySQL is 32bit version or 64 bit version

Finding whether MySQL is 32 bit version or 64 bit is possible with a simple query.

It is , show variables like 'version_compile_machine';

See the output from 32 bit version of MySQL.

mysql> show variables like 'version_compile_machine';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| version_compile_machine | ia32  |
+-------------------------+-------+
1 row in set (0.00 sec)


For 64bit, I believe the value will be 'x86_64'.

SQL Joins - Understand with Venn Diagram

SQL joins simply explained with Venn diagram. Found it very useful, so thought of sharing with you guys.

Cricinfo scores widget

International Scores: Get the latest scores of all the international cricket matches from Cricinfo. Add the Cricinfo International Scores widget now!

How to find MSSQL transaction log size

What is MSSQL transaction log :

Transaction log is basically used to write the transactions before committing them to database.  Never know the usage till now, even though used this term lot of times.

Why we need to know about the transaction log size.

Because this log file will grow and most of us are not aware of how much is being used and how much is left. So if the transaction log is full, the application using the database will not be able to write or read from it.

How to find the size. 

Simple. Connect to MSSQL studio and execute 'dbcc sqlperf(logspace)' .
Columns to look at is Log Size (MB)  and Log Space Used(%)

1.  Log Size (MB) - Current total log size for that table in MB.
2 . Log Space Used(%) - This will tell the used percentage with respect to the allocated/max size.

If some thing is greater than 90%, then probably you have to think of shrinking the log file or extending the MAX/allocated size.

Any other way to find this.

Connect to MSSQL studio, select the databases -->> right click --> Properties -->Files .

-Krishna.