The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

50 top high CPU queries

select top 50

st.objectid, st.dbid,

— object_name(st.objectid, st.dbid), — uncomment this line if you’re using SQL 2005 SP2

total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from

sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

Advertisements

June 22, 2009 Posted by | Scripts, SQL SERVER | , , , , , , , | Leave a comment

The old argument between Programmer and DBA

Within my work experience, there is the “old argument” between DBA and programmer. The programmer does not always understands why the DBA would not give him

permissions for executions or DML operations in the DB (INSERT, UPDATE and DELETE) .

The DBA, as a data protector, tries to do his best in protecting the database, and the programmer from the other hand wants

an access to the db, as he wants to develop new stuf and writing some T_SQL with no help from the DBA. I have to say, that although the DBA does and really wants to help the

programmer, main part of his job is to protect the data and manage permissions.

I would say, that the best solution is to give the programmer a way to work in front of the DB, but on the other hand not to give the programmer a SYSADMIN role 🙂

June 22, 2009 Posted by | General | , , , , | Leave a comment

Update statistics All Tables Script

SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = ‘BASE TABLE’
OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N’UPDATING STATISTICS ‘ + @tablename
SET @Statement = ‘UPDATE STATISTICS ‘  + @tablename –+ ‘  WITH FULLSCAN’
–print @Statement
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

June 22, 2009 Posted by | Administration, Scripts, SQL SERVER | , , | Leave a comment