Best Practices are good enough to follow, but simply do not read and digest. Try to implement them within your environment to keep up the performance. Similary there are many things involved within the SQL Server 2005 Analysis Services such as MDX queries, data mining etc. For any sort of performance analysis exercise you must identify the ...

You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested triggers server configuration option. So how it can be escaped using CLR and how procedural and ...

When I'm performing a performance analysis on a 24/7 application and dealing with PSS I had been given the following TSQL to identify the long running queries against a database. select r.session_id, s.host_name, s.program_name, s.host_process_id, r.status, r.wait_time,wait_type,r.wait_resource, substring(qt.text,(r.statement_start_offset/2) +1, ...

Cursors are based for a definitive purpose, but heavy usage of such methods will prove as costly expense on database performance. The process of cursor is prolonged, as a cursor first has to be defined with its features set, then populated after positioning (scrolled) to a set of record(s) and fetched every time. Finally once the process is ...

As you may aware the PERFMON (SYSMON) utility provides much information to analyze on the systems resource usage, I support and suggest to make use of this tool as much as you can with a default templates within our environment. You may be aware the data is saved as a file with .BLG extension which can be opened again using this SYSMON tool. I ...

select p . state_desc + ' ' + p . permission_name + ' OBJECT::' + s . name collate Latin1_general_CI_AS + o . name collate Latin1_general_CI_AS + ' TO ' + u . name collate Latin1_general_CI_AS + 'GO' , p .* from sys.database_permissions p inner join sys.objects o on p . major_id = o . object_id inner join sys.schemas s on s . schema_id = o . ...

SELECT sid, status, name, dbname, hasaccess, loginname FROM master.dbo.syslogins; GO SELECT spid, kpid, lastwaittype, waitresource, dbid FROM master.dbo.sysprocesses; GO...(read more)

SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC...(read more)

Integration with .NET framework CLR within SQL Server gives extra flexibility for the developers to use other programming languages such as C# or VB.net by creating functions, stored procedures, triggers, data types, and aggregates are among the kinds of business logic that programmers can write with these languages. But think about performance ...

You may be aware using SP_WHO or SP_WHO2 statements you can get information about a session id and statement that is running since good olden days of SQL Server. Within SQL 2005 version you can take help of TSQL: SELECT session_id, text FROM sys.dm_exec_requests s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 Similarly you can look at ...