Who is Active ? October 25, 2010
Posted by furrukhbaig in DMV's, Execution Plan, Optimize, Queries running on the server, sp_who, sp_who2, sp_whoisactive, system monitoring, Tips, XML Execution Plan.Tags: dmv, optimize, sp_who, sp_who2, sp_whoisactive, system monitoring, xml execution plan
add a comment
Adam Machanic has released new build of system monitoring stored procedure SP_WhoIsActive. Its really useful and I would like to congratulate him for his efforts.
check out the link below for his original post and to download the code.
Enjoy !!
Rowcount for Large Tables July 24, 2008
Posted by furrukhbaig in DMV's, Performance, SQL Server 2005, SQL Server 2005 features, Tips, TSQL.Tags: large tables, rowcount, rowcount scan, sys.dm_db_partition_stats
15 comments
Ever wondered why simple statements like SELECT COUNT(*) FROM [Table_Name] takes forever to return row count on large tables? Its because it uses full table scan to count number of rows. The instant way to get the row count on any table is to query new Dynamic Management Views (DMV) in SQL Server 2005 sys.dm_db_partition_stats. DMV contains row count and page counts for any table including all the partitions. Please note even you did not create partition for your table, your table still going to be created on single default partition so this will include all the tables.
Following are some useful queries that will return row count almost instantly. In SQL Server 2000 sysindexes system table is used to get the number of rows for large table to avoid full table scan.
Comments are welcome.
— All the tables having ZERO rows
— compatable with partitioned tables
SELECT
Table_Name = object_name(object_id),
Total_Rows = SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
GROUP BY
object_name(object_id)
HAVING
SUM(st.row_count) = 0
ORDER BY
object_name(object_id)
— Row Count without doing full table Scan
— This will include provide total number of rows in all partition (if table is partitioned)
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
object_name(object_id) = ‘Your_Table_Name_Goes_Here’
AND (index_id < 2) — Only Heap or clustered index
SSMS slow startup March 7, 2008
Posted by furrukhbaig in Performance, slow start, SQL Server 2005, SSMS, Tips.Tags: slow ssms, sql server management studio slow, ssms slow startup
add a comment
Friend of mine asked me “why SSMS is taking up to a minute to startup” on his office machine and I was not able to figure out the problem untill I find the post on Euan Garden’s Blog.
http://blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx
The quick fix is to add following lines in host file
# entry to get around diabolical Microsoft certificate checks
# which slow down non internet connected computers
127.0.0.1 crl.microsoft.com
This is to do with certificate validity check. It fix the problem with my friends machine. Hope it helps others too.
Enjoy !!
Index Fragmentation and dm_db_index_physical_stats December 12, 2007
Posted by furrukhbaig in DMV's, dm_db_index_physical_stats, Fragmentation, Index Fragmentation, Index tuning, Index usefulness, Indexes, Optimize, Performance, Performance Tuning, SQL Server 2005, SQL Server 2005 features, Tips.Tags: clustered on business key, compound clustered index fragmentation, dm_db_index_physical_stats, fillfactor, find out fragmentation, index fragmentation, sql server fragmentation
1 comment so far
Fragmentation can cause serious performance issues and its vital to understand how this can impact performance before I tell you how to fix it.
Fragmentation can cause slow running queries due to more disk IO then usual which itself cause by page spliting. Fragmentation not only cause query performance it can also slow down write operation such as INSERT, DELETE and UPDATE.
SQL Server write data on 8k data pages. This limit can be reached very quickly, for wide tables storing lots of varchar/nvarchar columns (nvarchar take 2 bytes to store 1 character), will cause request for more data pages. When SQL Sever uses whole page then it occupy another page to store more data. If table has got cluster index then data needs to be stored in sorted order as cluster index ensure physical order of data according to the cluster key and thats why you can only have one clustered index per table.
Now assume you have compound clustered index on CountryID and CustomerNo colums on a transactional table which recieve 10 transactions per minute for an online car rental portal. Clustered index will make sure that data is stored in sorted order on CountryID and CustomerNo. Having millions of rows in table consider what will happen when you recieve transaction row which needs to be inserted on page 1 (CountryId = 1, CustomerId = 100) . Above will require to make some room on 1st data page (depending on FILLFACTOR, default is 100%). If require data page has not got enough room for the row to fit then rows on the data pages needs to be move forward (called PageSplit) in order to make some room for new row to fit on page in sorted order. This can cause lot of page split and fragmentation as new pages required (caused by page splits on existing data pages) can not be guarantee to be in sequence with other page. This can seriously hurt performance for data modification (due to pagesplit) and data read (due to out of order pages) operations.
dm_db_index_physical_stats can be used to check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N’YourDB’), OBJECT_ID(N’dbo.CarRental’), NULL, NULL , ‘DETAILED’);
The above SQL will return fragmentation information for given Database name (Parameter 1) and Table Name (Parameter 2). Last parameter suggest the mode of analysis.
It is good practice to create clustered indexes on incrementing value (e.g. IDENTITY) to avoid page splits and out of order pages.
SSIS / SQL Server / Performance Tuning 