The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Defragmenting Indexes in SQL Server 2005

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

I think this may come in handy…

Advertisements

June 26, 2009 Posted by | General, SQL SERVER, Tips | , , | Leave a comment

Tweet-SQL

Integrate your application in super quick time using Tweet-SQL. Tweet-SQL promises to allow fast and easy Twitter Integration through the use of CLR stored procedures on Microsoft SQL Server. A quick summary of Tweet-SQL…

http://sqlclrnews.blogspot.com/2008/09/easy-twitter-integration-with-tweet-sql_3653.html

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

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

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

SAVE XML EXCEUTION PLAN IN GRAPHIC WAY

Some DBA’s need to save the execution plan in graphic way. In order to do so you need to:

1.save your exceution query XML as filename.SQLPLAN

2. open the file with management studio, which open it in a graphic way instead of XML.

June 13, 2009 Posted by | SQL SERVER, Tips | , , , | Leave a comment

Grant Permissions script

/*this script will give you execution statements for all objects under your DB. Simply replace the DbAdmin with the user you want to give permissions to and that is it.*/

set nocount on

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘U’

and¬†¬†¬†¬†¬†¬† name not like ‘dtp%’

select ‘grant select, insert, update, delete on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘V’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘P’

and¬†¬†¬†¬†¬†¬† name not like ‘dt%’

select ‘grant exec on ‘ + name + ‘ to DbAdmin’ + char(13) + ‘go’

from     sysobjects

where¬†¬† type = ‘FN’

and¬†¬†¬†¬†¬†¬† name not like ‘dt%’

June 12, 2009 Posted by | Permissions | , , , | Leave a comment