BPSoftware.com Blog

Tuesday, November 25, 2008

SQL: DBCC CHECKTABLE on multiple tables

Part of decent SQL database maintenence is periodically checking the integrity, both logical and physical, of database objects. SQL Server has the DBCC CHECKDB command that will check the integrity of the objects and report back the results. DBCC CHECKDB runs CHECKALLOC, CHECKTABLE and CHECKCATALOG on the specified database.

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

in SQL 2005 you can use table variables if you do not need or want to store the data you can store it in a table variable instead of a temporary table

DBCC CHECKTABLE allows you to check the integrity of a specified table or indexed view. If there are a large number of tables to check, the task of running the command for each tablename could be time consuming. Creating a script that loops through a set of database tables and runs DBCC CHECKTABLE for each, is a bit easier.

The sys.objects contains each of the user-defined schema objects in a database. Rows that are of [Type] = 'U' are tables. This makes it easy to get all the tables within a database (of course other filters can be applied). The following script will loop through and run the DBCC CHECKTABLE command for each table in the database (the command can be easily replaced to perform another action on each returned table):

-- set options
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- declare table to store table names
10

-- in SQL 2005 you can use table variables
11

-- if you do not need or want to store the data you can store it in a table
12

-- variable instead of a temporary table
13

DECLARE @tablenames TABLE (
14

TableName VARCHAR(255)
15

);
16

-- populate table with tablenames
20

INSERT INTO @tablenames
21

SELECT
22

name
23

FROM
24

sys.objects
25

-- additional filters can be set to narrow table selection
26

WHERE
27

([type] = 'U');
28

-- Declare a cursor to loop through table
32

DECLARE tablenames INSENSITIVE SCROLL CURSOR
33

FOR
34

SELECT
35

TableName
36

FROM
37

@tablenames;
38

-- Open the cursor.
43

OPEN tablenames
44

-- declare variables
48

DECLARE @curtablename AS VARCHAR(255);
49

DECLARE @cmd AS VARCHAR(1024);
50

-- Loop through all the tables
54

WHILE (1=1)
55

BEGIN
56

FETCH NEXT FROM tablenames INTO @curtablename;
57

IF @@fetch_status <> 0 BREAK;
58

-- run the command
59

set @cmd = 'DBCC CHECKTABLE ([' + rtrim(@curtablename) + '])';
60

exec (@cmd)
61

print @cmd + CHAR(10) + CHAR(13);
62

end
63

-- Close cursor
67

CLOSE tablenames;
68

DEALLOCATE tablenames;
69

Labels: Code, SQL

posted by Brad Prendergast at 9:04:00 PM (0 comments)
Links to this post
Permalink

Monday, November 24, 2008

SQL: Index Fragmentation Maintenance

In a previous post, I discussed SQL Server's built in ability for suggesting missing indexes. As records are inserted, updated and deleted from tables indexes can become fragmented.

A fragmented index affects performance (table scans and index seeks) when the DBMS needs to search for records.

This probably leaves the question, "How can one defragment the indexes of SQL tables?"

Within SQL Server 2005 and greater you can setup a maintenance plan to defragment all the indexes within all or a particular database. You can also right click on an index and select to either Rebuild or Reorganize and index (rebuilding will re-create the index whereas reorganize reorders the index pages).

I prefer to have a little more control and use the following script as part of my maintenance plan. This script allows for a little more control in the selecting of tables, indexes and databases to maintain. I tend to rebuild indexes that are more than 30% fragmented and reorganize those that are greater than 5%.

SQL Server 2005 (and greater) has index information more readily accessible. The key to this script is the index information that is contained in sys.dm_db_index_physical_stats. This table returns the size and fragmentation information for each index.

/** **/
2

-- Create table to hold index information
3

SET NOCOUNT ON;
4

create table _IndexInformation (
6

database_id smallint,
7

ObjectName varchar(255),
8

[object_id] int,
9

IndexName varchar(255),
10

[index_id] int,
11

index_level int,
12

index_type_desc varchar(60),
13

avg_fragmentation_in_percent float,
14

fragment_count float,
15

page_count bigint,
16

record_count bigint,
17

index_depth int
18

);
19

/** **/

/** **/
2

-- clear table contents
3

truncate table _IndexInformation;
4

-- populate table
6

insert into _IndexInformation
7

select
8

database_id,
9

object_name(i.object_id) AS [ObjectName],
10

i.[object_id],
11

i.name AS [IndexName],
12

dm.index_id,
13

dm.index_level,
14

dm.index_type_desc,
15

dm.avg_fragmentation_in_percent,
16

dm.fragment_count,
17

dm.page_count,
18

dm.record_count,
19

dm.index_depth
20

from
21

-- database, table and index information can be specified
22

/*
23

sys.dm_db_index_physical_stats (
24

{ database_id NULL 0 DEFAULT }
25

, { object_id NULL 0 DEFAULT }
26

, { index_id NULL 0 -1 DEFAULT }
27

, { partition_number NULL 0 DEFAULT }
28

, { mode NULL DEFAULT }
29

)
29

This example uses database 1; you should replace this with the ID of the database you wish to process
30

*/
31

sys.dm_db_index_physical_stats(1,null,null,null,'detailed') dm
32

INNER JOIN sys.indexes i ON
33

(i.object_id = dm.object_id) AND (i.index_id = dm.index_id);
34

/** **/
35

/** **/
2

-- read index table and maintain indexes
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- Declare variables.
10

declare @tablename as varchar(255)
11

declare @indexname as varchar(255)
12

declare @frag as float
13

declare @cmd as varchar(1024)
14

-- Declare a cursor.
16

declare indexes insensitive scroll cursor
17

for
18

select
19

ObjectName,
20

IndexName,
21

avg_fragmentation_in_percent
22

from
23

_IndexInformation
24

where
25

(avg_fragmentation_in_percent >= 5);
26

-- Open the cursor.
28

open indexes
29

-- Loop through all the indexes.
32

while (1=1)
33

begin
34

fetch next from indexes into @tablename, @indexname, @frag;
35

if @@fetch_status <> 0 break;
36

-- if fragmentation is above 30% rebuild index
37

-- if fragmentation is above 5% reorganize index
38

if (@frag >= 30)
39

begin
40

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REBUILD;'
41

exec (@cmd)
42

print @cmd;
43

end
44

else
45

if (@frag >= 5)
46

begin
47

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REORGANIZE;'
48

exec (@cmd)
49

print @cmd;
50

end;
51

end
52

-- Close cursor
56

close indexes;
57

deallocate indexes;
58

/** **/
59

/** **/
2

-- delete index table
3

drop table _IndexInformation;
4

/** **/
5

I have the script schedule to as part of my database maintenance plan. I run the script several times with additional selection criteria limiting it to groups of tables in a particular database.

Labels: Code, SQL

posted by Brad Prendergast at 9:51:00 PM (2 comments)
Links to this post
Permalink

Thursday, November 20, 2008

SQL: Where are the database files?

From within the SQL Server Management Studio you can right click on a database and view its properties to get a list of the physical files that make up a database. If you do not have access to SQL Management Studio and the database is online you can run the sp_helpfile stored procedure. The sp_helpfile stored procedure lists the files for the current (individual) database; the database must be online. What about retrieving file information for offline databases?
Another alternative for retrieving the list of database files for a single or collective set of databases is to query the sys.master_files table. You can query the sys.master_files database if the database is online, offline or in one of the other states. The following query retrieves database file information:

SELECT
2

database_id,
3

DB_Name(database_id) as database_name,
4

[file_id],
5

type_desc,
6

[name],
7

physical_name,
8

state_desc,
9

[size],
10

max_size
11

FROM
12

sys.master_files
13

WHERE
14

-- you can use the DB_ID function to return the
15

-- database id of a database by name
16

(database_id = DB_ID(N'master'))
17

-- if you know the id you can filter by it directly
18

-- (database_id = 1)
19

ORDER BY
20

type_desc DESC,
21

physical_name;

The sys.databases table has information about the database in the current SQL Server instance.

SELECT
2

database_id,
3

[name],
4

compatibility_level,
5

collation_name,
6

state_desc
7

FROM
8

sys.databases;

Labels: Code, SQL

posted by Brad Prendergast at 9:08:00 PM (0 comments)
Links to this post
Permalink

Wednesday, November 12, 2008

SQL: Remove / Delete Orphan Users

As discussed in the post SQL Delete/Drop a User from each Database, when you delete a Login from SQL Server it does not remove them from the databases they have explicit permissions on. If a user login is removed, and they are not removed from the database you would have orphan users with permission to the database. The stored procedure listed will remove (delete) orphan datatabase user logins.

1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7
8 create procedure [dbo].[sp_DeleteOrphanUsers]
9 AS
10
11 -- revoke orphan user
12 declare @cmd as varchar(1000)
13 declare @uname as sysname
14
15 DECLARE my_cur INSENSITIVE SCROLL CURSOR
16 FOR
17 -- orphan user
18 select u.name from master..syslogins l right join
19 sysusers u on l.sid = u.sid
20 where l.sid is null and issqlrole <> 1 and isapprole <> 1
21 and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest' and u.name <> 'sys'
22 and u.name <> 'system_function_schema')
23 order by u.name
24
25 open my_cur
26
27 WHILE (1 = 1)
28 begin
29 FETCH FROM my_cur INTO @uname
30 IF @@fetch_status <> 0
31 BREAK
32 set @cmd = 'exec sp_revokedbaccess ''' + rtrim(@uname) + ''''
33 print @cmd
34 exec (@cmd)
35
36 end
37 DEALLOCATE my_cur
38
39 return(0)

The procedure would be executed like the following:

1 use [databasename] /* where databasename is the name of the database to run the procedure on */
2 exec sp_DeleteOrphanusers

Labels: Code, How-To, SQL

posted by Brad Prendergast at 6:04:00 AM (2 comments)
Links to this post
Permalink

Tuesday, November 11, 2008

SQL Delete/Drop a User from each Database

Deleting a user from SQL server deletes them from the security system, however it does not remove them from databases they have 'securables' (permissions speficified) for. The following procedure will remove a user from each database and remove their account.

1 USE [master]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 CREATE procedure [dbo].[sp_DeleteUser]
8 @username sysname = NULL
9 AS
10
11 -- declare @nam as sysname
12 declare @cmd1 as varchar(500)
13
14 if @username is not null
15 begin
16 set @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
17 exec sp_msforeachdb @command1= @cmd1
18 exec ('DROP LOGIN ' + @username)
19 return(0)
20 END
21 return (1)

The stored procedure is called like;

1 exec sp_DeleteUser 'loginname' /* where loginname is the name of the account that you would like deleted */

Labels: Code, How-To, SQL

posted by Brad Prendergast at 8:45:00 PM (0 comments)
Links to this post
Permalink

Monday, November 10, 2008

SQL Optimization: Am I missing any indexes? - Part I

Lately, I have been doing a lot with SQL (2005) optimization. Database design plays an important role in the efficiency of a database. A key part of the design is the use of indexes. Indexes significantly affect the performance of a database whether it is during a SELECT, INSERT, UPDATE and DELETE Transact-SQL (DML) statement. The biggest gain from an index is when a user SELECTs data from a database. The designer of the database and application should have done their best to come up with indexes that accommodate how the database will be used. How can one be sure they’re not missing any indexes?

SQL Server 2005 has a feature that keeps track of Missing Index Information. This information is contained in the sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details tables. The missing index information in these tables can be used to determine indexes that would improve database performance based upon its usage. The following query returns the indexes that would improve database performance. Before creating the indexes review the usage values to determine if the creation of the index is really necessary.

1 /* missing indexes*/
2 use [master]
3 SELECT IG.INDEX_GROUP_HANDLE,
4 ID.INDEX_HANDLE,
5 ID.DATABASE_ID,
6 DB_NAME(ID.DATABASE_ID) AS DATABASE_NAME,
7 ID.[OBJECT_ID],
8 OBJECT_NAME(ID.[OBJECT_ID],ID.DATABASE_ID) as TABLE_NAME,
9 ID.STATEMENT AS FULL_TABLE_NAME,
10 ID.EQUALITY_COLUMNS,
11 ID.INEQUALITY_COLUMNS,
12 ID.INCLUDED_COLUMNS,
13 GS.USER_SEEKS,
14 GS.USER_SCANS,
15 GS.LAST_USER_SEEK,
16 GS.LAST_USER_SCAN,
17 GS.AVG_TOTAL_USER_COST,
18 GS.AVG_USER_IMPACT,
19 GS.SYSTEM_SEEKS,
20 GS.SYSTEM_SCANS,
21 GS.LAST_SYSTEM_SEEK,
22 GS.LAST_SYSTEM_SCAN,
23 GS.AVG_TOTAL_SYSTEM_COST
24 FROM SYS.DM_DB_MISSING_INDEX_DETAILS AS ID
25 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUPS AS IG
26 ON ID.INDEX_HANDLE = IG.INDEX_HANDLE
27 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS GS
28 ON GS.GROUP_HANDLE = IG.INDEX_GROUP_HANDLE
29 ORDER BY ((GS.AVG_TOTAL_USER_COST * GS.AVG_USER_IMPACT) * (GS.USER_SEEKS + GS.USER_SCANS)) DESC
30
31

Using the above query result you could create the necessary index (the field names are listed where their values should be used):

1 CREATE NONCLUSTERED INDEX <unique index name>