Posted At : Dec 07, 2009 0:39 AM | Posted By : Ed Tabara
Related Categories: ColdFusion, cfHSSF, cfWatcher, cfFirewall, My Projects, cfSQLMaster, 1ssChat, 1ssBlog

WOOHOO!!!!!!!!!!!!!!!!!!!!! 6000 downloads of my projects. Not too bad. :) ...

| 6283 Views | 7% / 0% Popularity


Posted At : May 29, 2007 19:28 PM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

I added a new project - cfSQLMaster, that i worked on for some time when resting from my usual work. This is a set of functions that should help you understand better what is going on with your MSSQL 2005 DB and will allow also for DB optimization. For example, you can get DB info, you can see what indexes are not used, what indexes you may need to add in order to have the DB work better/faster, etc. I really hope it will be useful for someone....

| 7282 Views | 8% / 0% Popularity


Posted At : Apr 02, 2007 12:18 PM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

Today i will show 2 functions that may help with getting information about fragmentation of your data and indexes in MSSQL. First function will displays fragmentation information for the data and indexes of the specified mssql table. It take 3 arguments: DB name, table name and if you want to see the data for each particular index or as a general report. use #DBName# DBCC SHOWCONTIG(#DBObjName#) with tableresults, ALL_INDEXES And this function will returns some fragmentation statistics as being the size and fragmentation. Possible parameters are: DB name, table name, index name (if not provided it will show the info for all indexes), mode (possible values are Limited and Detailed) and fragmentation value from which to show the result. DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'#DBName#'); SET @object_id = OBJECT_ID(N'#DBObjName#'); IF @db_id IS NULL BEGIN...

| 5659 Views | 7% / 0% Popularity


Posted At : Mar 22, 2007 19:35 PM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

So, you will get the list of indexes not used since the last time SQL Server was recycled. use #DBName# Select object_name(i.object_id) as objName, i.name as indexName From sys.indexes i, sys.objects o Where i.index_id NOT IN ( Select s.index_id From sys.dm_db_index_usage_stats s Where s.object_id=i.object_id and i.index_id=s.index_id ) and o.type = 'U' and o.object_id = i.object_id and o.object_id = object_id('#DBObjName#') Order by object_name(i.object_id) asc ...

| 8461 Views | 10% / 0% Popularity


Posted At : Mar 22, 2007 19:26 PM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

This script will lists statistics for existing indexes. use #DBName# Select s.*, db_name(s.database_id) as dbName, object_name(s.object_id) as objName, i.name as index_name From sys.dm_db_index_usage_stats s, sys.indexes i, sys.objects o Where i.index_id = s.index_id and o.type = 'U' and o.object_id = i.object_id and s.object_id = object_id('#DBObjName#') and i.name = '#indexName#' Order by user_updates desc ...

| 6826 Views | 8% / 0% Popularity


Posted At : Mar 21, 2007 4:52 AM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

Sometimes in order to identify a problem with your indexes, may needed to be done few statistics snapshots within particular interval. To do this i wrote this quick function based on examples i found on Microsoft's blogs. declare @dbid int select @dbid = db_id('#DBName#') declare @objid int select @objid = object_id('#DBObjName#') select s.database_id, db_name(s.database_id) as dbName ,s.object_id, object_name(s.object_id) as objName ,s.index_id ,s.partition_number ,s.leaf_insert_count ,s.leaf_delete_count ,s.leaf_update_count ,s.leaf_ghost_count ,s.nonleaf_insert_count ,s.nonleaf_delete_count ,s.nonleaf_update_count ,s.leaf_allocation_count ,s.nonleaf_allocation_count ,s.leaf_page_merge_count ,s.nonleaf_page_merge_count ,s.range_scan_count ,s.singleton_lookup_count ,s.forwarded_fetch_count ,s.l...

| 8631 Views | 10% / 0% Popularity


Posted At : Mar 21, 2007 4:48 AM | Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL

To list all idexes wit the most blocking in MSSQL 2005 you may use the following code: use #DBName# declare @dbid int select @dbid = db_id() Select dbid=database_id, objectname=object_name(s.object_id) , indexname=i.name, i.index_id , row_lock_count, row_lock_wait_count , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)) , row_lock_wait_in_ms , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s ,sys.indexes i where objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id and s.object_id = object_id('#DBObjName#') order by row_lock_wait_count desc ...

| 5635 Views | 7% / 0% Popularity