http://blog.1SmartSolution.com/1SmartSolution Blog1SmartSolution Blog6000 downloadsWOOHOO!!!!!!!!!!!!!!!!!!!!!
6000 downloads of my projects. Not too bad. :)
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=229&6000-downloads
Mon, 07 Dec 2009 00:39:00 -0400WOOHOO!!!!!!!!!!!!!!!!!!!!!
6000 downloads of my projects. Not too bad. :)
cfSQLMaster - some MSSQL optimization done from ColdFusionI 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.
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=63&cfSQLMaster--some-MSSQL-optimization-done-from-ColdFusion
Tue, 29 May 2007 19:28:00 -0400I 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 MSSQLGet fragmentation information for the data and indexes with ColdFusion and MSSQLToday 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 retu
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=48&Get-fragmentation-information-for-the-data-and-indexes-with-ColdFusion-and-MSSQL
Mon, 02 Apr 2007 12:18:00 -0400Today 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 indList any index not used since the last time SQL Server was recycledSo, 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_
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=41&List-any-index-not-used-since-the-last-time-SQL-Server-was-recycled
Thu, 22 Mar 2007 19:35:00 -0400So, 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 syList statistics for existing MS SQL 2005 indexes with ColdFusionThis 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
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=40&List-statistics-for-existing-MS-SQL-2005-indexes-with-ColdFusion
Thu, 22 Mar 2007 19:26:00 -0400This 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
MSSQL 2005 index statistics snapshots done from ColdFusionSometimes 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
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=34&MSSQL-2005-index-statistics-snapshots-done-from-ColdFusion
Wed, 21 Mar 2007 04:52:00 -0400Sometimes 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 foundMSSQL 2005 - lists indexes with the most blocking using ColdFusionTo 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 + ro
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=33&MSSQL-2005--lists-indexes-with-the-most-blocking-using-ColdFusion
Wed, 21 Mar 2007 04:48:00 -0400To 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, objeMS SQL 2005 + ColdFusion: identify potentially useful indexesToday's piece of code identifies potentially useful indexes and makes recommendations for changes:
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
and d.statement = '[#DBName#].[#DBUserName#]
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=31&MS-SQL-2005--ColdFusion-identify-potentially-useful-indexes
Tue, 20 Mar 2007 10:50:00 -0400Today's piece of code identifies potentially useful indexes and makes recommendations for changes:
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.lasGet the number of connections to the MS SQL 2005 server done from ColdFusionTo get the number of connections to the MS SQL 2005 server may be used the following function:
SELECT session_id, connect_time, net_transport, num_reads, num_writes, last_read, last_write
, client_net_address, client_tcp_port, local_net_address, local_tcp_port
FROM sys.dm_exec_connections;
Example of usage:
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=30&Get-the-number-of-connections-to-the-MS-SQL-2005-server-done-from-ColdFusion
Mon, 19 Mar 2007 08:05:00 -0400To get the number of connections to the MS SQL 2005 server may be used the following function:
SELECT session_id, connect_time, net_transport, num_reads, num_writes, last_read, last_wriMS SQL 2005: reports information about how busy SQL Server has been - done with ColdFusion.Here is a new piece of code. This one will help to get reports information about how busy SQL Server has been.
Example of usage:
Note that you will need special permissions to run this script.
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=29&MS-SQL-2005-reports-information-about-how-busy-SQL-Server-has-been--done-with-ColdFusion
Mon, 19 Mar 2007 07:56:00 -0400Here is a new piece of code. This one will help to get reports information about how busy SQL Server has been.
Example of usage:
Note that you willMS SQL 2005 tricks (part 2)Here are the next 2 scripts. It was't too long between posts, huh?
This will return the Total Physical Memory:
use master
select physical_memory_in_bytes/(1024.0*1024.0*1024.0) as physical_mem_in_gb
from sys.dm_os_sys_info
And this one will return the Number of Processors:
use master
select cpu_count from sys.dm_os_sys_info
Cya soon...
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=27&MS-SQL-2005-tricks-part-2
Mon, 19 Mar 2007 03:41:00 -0400Here are the next 2 scripts. It was't too long between posts, huh?
This will return the Total Physical Memory:
use master
select physical_memory_in_bytes/(1024.0*1024.0*1024.0) as pMS SQL 2005 tricksI am starting a series of posts of things you may do in MS SQL 2005. Hope it will help someone.
In a some later time i am going to publish a CFC with all kind of functions that may help optimizing MSSQL2005 ond your sql scripts.
Ok, ok.. too much talk. :)
Here is first script:
Select *, suser_sname(owner_sid)
From sys.databases
Where database_id =
Where name =
Example of usage:
http://blog.1SmartSolution.com//index.cfm?action=posts.entry&id=26&MS-SQL-2005-tricks
Mon, 19 Mar 2007 02:47:00 -0400I am starting a series of posts of things you may do in MS SQL 2005. Hope it will help someone.
In a some later time i am going to publish a CFC with all kind of functions that may help optimizing M