Rebuild all indexes of a database at one go

Use YOURDBNAME
-- Declare variables

SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

 

Ref Microsoft Best Practices - Indexes

 

I saw a nice post today, can't resist myself from copying and publishing it for my readers

Are You a Project Manager Or a Project Mangler?

Which one are you? An effective IT project manager, able to deliver software on time, according to specs, and within budget, or someone referred to by your peers as a project mangler? Find out with these Top 10 Signs You're a Project Mangler.
10. Your .mpp attachments are considered to be more harmful than the Netsky virus.
9. You think your job description is limited to running around and asking people "Are you done yet?"
8. Your record for the "longest consecutive number of days without changing your project plan" is 3, which was achieved over a weekend.
7. You don't publish your project plan for fear developers might find out what the REAL dates are.
6. When the first 90% of your project is done, the second 90% begins.
5. You couldn't write a line of code to save your life, yet you tell developers how long it will take them to complete their feature.
4. You only work from 9 to 5 but expect developers to work evenings and weekends to meet your deadlines.
3. Your best motivational skill: telling people you're working from home tomorrow.
2. You DO think that 9 people can have a baby in 1 month.
And the number one sign you're a project mangler...
1. Your name is R. U. Dunyet.

Avoid Dynamic Queries

 

A sample dynamic query goes like this,

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId AS  INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

DECLARE @sSQLSearch AS VARCHAR(1000)
SET @sSQLSearch = ' SELECT *  FROM tbl_Employee '
SET @sSQLSearch = @sSQLSearch + ' WHERE 1 = 1  AND ' 

IF @iEmployeeId <> 0
BEGIN
SET  @sSQLSearch = @sSQLSearch + ' EmployeeID = ' + CAST(@iEmployeeId AS  VARCHAR)
END

IF @sEmployeeName <> 0
BEGIN
SET @sSQLSearch = @sSQLSearch + ' EmployeeName = ' + @sEmployeeName
END

EXEC(@sSQLSearch)

END

Instead of the above Query, reforming the query without using Dynamic SQL can be done like this:

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId  AS INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

SELECT * FROM tbl_Employee
WHERE
(
@iEmployeeId = 0  OR
EmployeeId = @iEmployeeId
) 
AND
(
@sEmployeeName = '' OR
EmployeeName = @sEmployeeName
)

END

 ---------------------------------------------------------------------------------------

Dynamic SQL can be created easily but it comes along with a few drawbacks:

 
1. The main advantage of using Stored Procedures in SQL is Precompiliation. All the SPs are stored in a precompiled form and hence they are faster in execution. On the contrary, the main SELECT query in a dynamic SQL is NOT Precompiled. This hampers the performance.
2. The maximum length EXEC can handle is 8000 (if VARCHAR is used to build the query). Queries going beyond the limit can not be executed.

 

Wise Computing!!

Save Server Power, Save Energy, Save Earth

To check the space used by all tables in a database

USE master
GO
CREATE PROC dbo.sp_DBA_spaceused_AllTables
AS
SET NOCOUNT ON

-- Temporary table to catch the output from sp_DBA_spaceused
CREATE TABLE #T (
 TableName SYSNAME NOT NULL,
 NumberOfRows BIGINT NOT NULL ,
 Reserved BIGINT NOT NULL ,
 Data BIGINT NOT NULL ,
 IndexSize BIGINT NOT NULL ,
 Unused BIGINT NOT NULL 
)
 
-- We are only interested in user databases
IF DB_NAME() NOT IN ('master','model','msdb','tempdb','distribution')
 BEGIN
  DECLARE @UserTables TABLE(TableName NVARCHAR(256) NOT NULL)

  INSERT INTO @UserTables
  SELECT USR.name + '.' + OBJ.name
  FROM dbo.sysobjects AS OBJ
   INNER JOIN
   sys.schemas AS USR
   ON OBJ.uid = USR.schema_id
  WHERE OBJ.type='U'

-- loop through all tables without using a cursor.
  DECLARE @TableName NVARCHAR(256)
  SET @TableName=''
  WHILE @TableName IS NOT NULL
   BEGIN
    SELECT @TableName=MIN(TableName)
    FROM @UserTables
    WHERE TableName>@TableName

    IF @TableName IS NOT NULL
     BEGIN
      INSERT INTO #T
      exec sp_dba_spaceused @TableName
     END
   END
 END

-- return our records
SELECT
 DB_NAME() AS DatabaseName,
 GETDATE() AS DateCreated,
 TableName,
 NumberOfRows ,
 Reserved,
 Data,
 IndexSize,
 Unused

FROM #T

-- clean up after ourselves.
DROP TABLE #t
GO

After you finish creating this stored procedure, you can run against each database

exec master.dbo.sp_msforeachdb @command1="exec [?].dbo.sp_DBA_spaceused_AllTables"

Boosting SQL Server throughput - Part I

Recently, I came across nifty things, like DBCC PINTABLE ( putting sql server table's data pages in memory (RAM), they are stored in Physical disk (HDD) ) , excellent DMV goodies like db_missing_index_details, and TABLESAMPLE

All three are worthy of a detailed explanation here, but however, I am not a story freak, so my story telling about all three, will go short...

DBCC PINTABLE

Whenever, I get into any sort of SQL crisis, I usually find a DBCC command to probe it, or to fix it. My story begins like this .... My Project leader, Mr. Arun Patel, approached me, he had a damn slow report dashboard, ( fully loaded with 4 big charts, and couple of divs with nested datagrids), showing data in condensed ( everywhere count and group by aggregations ) form. :(

The worktables were 5 in number, and data rows were in millions.

My acumen, directed me to tear down stored procedures. I skimmed through queries, execution plan, did little tweaks like changing 'in' clause , setting nocount on, and several more ..... But, unfortunately could not get any luck, in terms of faster throughput. Now I moved to perfmon.exe to inquire into, what was getting cooked in server, when dashboard was getting loaded. I was taken aback, the 'average disk queue lengh' was surging like anything. Clearly showing hardware bottleneck of throughput, I asked my Project leader to get a high RPM, and high cache HDD, he returned 'We cannot raise our costs, we have already PUT more RAM and CPU strength' ----------------------||

Now, I had only thing in my mind, use bit of RAM, to get rid of Physical disk I/O bottleneck. I again suggested for putting Database files (mdf, ldf) in RAM DRIVE. My boss reverted, "We can not install third party RAM DRIVE software, they do no comply our data privacy policies". Again, moment of despair, :( I had suggest this because, we had 8GB of RAM, we were using only 3GB of it.  After spending a day of intense research and good luck ;), I was able to google out, DBCC PINTABLE, it keeps the data pages of tables in RAM, ( faster I/O).  

Syntax of pinning up the table in RAM is

DECLARE @db_id int, @tbl_id int
USE dbEmailFramework
SET @db_id = DB_ID('dbEmailFramework')
SET @tbl_id = OBJECT_ID('dbEmailFramework..tblfinalRecordbyDateRange')
DBCC PINTABLE (@db_id, @tbl_id)

 reversal of unpinning up the table in RAM is

DECLARE @db_id int, @tbl_id int
USE dbEmailFramework
SET @db_id = DB_ID('dbEmailFramework')
SET @tbl_id = OBJECT_ID('dbEmailFramework..tblfinalRecordbyDateRange')
DBCC UNPINTABLE (@db_id, @tbl_id)

and Kudos!!!!!!!!!!!!!!!!! It worked very interestingly. The page now started loading in 20 seconds, as compared with 3 minutes of earlier load time.  Other two will be covered in next two posts  

Caution : Never Use, DBCC PINTABLE unnecessarily. it requires a lot of Memory, check for table size also, with sp_spaceused 'tablename'. if it is too large, don't pin table

Javascript Frameworks


Last week, I came across these some javascript goodies. I wonder, if most of devs know about these javascript UI magics.




Some of them are listed here.

dhtmlx

Dojo

Echo3

Ext JS

Google Web Toolkit

jQuery

midori

MochiKit

MooTools

Prototype & script. aculo.us[1]

Pyjamas

qooxdoo

Rialto Toolkit

Rico

SmartClient & SmartGWT

SweetDEV RIA

YUI

ZK

My favourites among these are, Jquery, SmartClient.

You can also compare these,
Comparison of JavaScript frameworks