Monday, April 18, 2011

Tables creation script

Just completed a script which generates "create tables" script.

Yet to add "defaults", "constraints", "indexes"., 'll update the script after I add these as well.

This is just a basic script, it doesn't include identity but it has default constraints added.

I just found its too very complex to get an accurate script because you need to take into account whether the column is marked for replication, whether identity is set on it, if it is computed or not, foreign keys and so on.
I am sorry to leave this unfinished but I lost patience in just trying to correct it/rewrite it, so 'll continue later.
But this script is still treated as a basic one to generate table scripts

Declare @query nvarchar(4000)
Declare @tbl_name varchar(50)
declare @schema varchar(10)
declare @subquery nvarchar(2000)

Declare  Del_arch_tbls cursor for

    Select table_name,table_schema from information_schema.tables where table_type<>'view'
   
Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name,@schema

While @@fetch_status=0
begin

    declare @col nvarchar(30)
    declare @data_type nvarchar(10)
    declare @chr_length int
    declare @dt_precision int
    declare @isnull char(4)
    declare @collation nvarchar(40)
    declare @position int
    declare @num_precision int
    declare @col_query nvarchar(2000)
    declare @default varchar(100)
   
    --set @col_query=''
    Declare  cur_col cursor for

    Select column_name,data_type,isnull(character_maximum_length,0),is_nullable,isnull(numeric_precision,0),
    isnull(datetime_precision,0), isnull(collation_name,''),ordinal_position,isnull(column_default,'')
    from information_schema.columns where table_schema= @schema and table_name= @tbl_name
   
    open cur_col
    fetch next from cur_col into  @col,@data_type,@chr_length,@isnull,@num_precision,@dt_precision,
                            @collation,@position,@default
    while @@fetch_status=0
    begin

        -- 1.no need to add precision for datetime, int,smallint and bit, sql won't allow
        -- 2.actual generate script is not showing any collation levels for columns and hence
        -- commenting that code as well

        Set @col_query =  ' [' + @col + ']' + ' [' + @data_type + '] '
            if @chr_length >0
            begin
                set @col_query= @col_query + '('+cast(@chr_length as varchar)+') '
                if len(@default)>0
                    set @col_query= @col_query +  ' default ' + @default

                 --set @col_query= @col_query +  ' collate ' + @collation
            end
            else if @data_type='numeric'
            begin
                set @col_query = @col_query + '('+cast(@num_precision as varchar)+',0)'
                if len(@default)>0
                    set @col_query= @col_query +  ' default ' + @default
            end
--            else if @dt_precision >0
--            begin
--                set @col_query= @col_query + '('+cast(@dt_precision as varchar)+',0)'
--                if len(@default)>0
--                    set @col_query= @col_query +  ' default ' + @default
--            end
            if @isnull='yes'  set @col_query=@col_query + ' null'
            else
                set @col_query=@col_query + ' not null '
           
           
        --print @col_query
        --exec Sp_executesql @query
        --set @col_query=@col_query + ',' + @col_query
       
        if len(@subquery)>0
            set @subquery=@subquery + ',' + @col_query
        else
            set @subquery= @col_query

        fetch next from cur_col into  @col,@data_type,@chr_length,@isnull,@num_precision,@dt_precision,
                            @collation,@position,@default
       
    end
   
    Set @query = 'Create table [' + @schema + '].[' + @tbl_name  + '] (' + @subquery + ') on primary '

    print @query

    Close cur_col
    Deallocate cur_col

    set @subquery=''

    Fetch next from del_arch_tbls into @tbl_name,@schema

end


Close del_arch_tbls
Deallocate del_arch_tbls

Thursday, April 7, 2011

Audits in sql server 2008

Just noticed something new which started in sql server 2008.
Audits.

You can create and enable audits so all the related information like login success/failures, db backups/restores etc. will be logged in your audit file.
Its relatively simple to do.
I think this feature exists only in enterprise edition after sql server 2008

In order to create one here is the simple procedure:
1) create audit  by going to SSMS-> security tab->Audits tab
You can either choose the audit to be logged in windows application event log or security event log or your own file.

CREATE SERVER AUDIT [Audit-20110407-190438]
TO FILE
(    FILEPATH = N'W:\Audit\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '824f0cb7-328a-4875-89aa-dcf42ba7bceb'
)
GO

2) Once done, you need to create server audit specifications where each one should be associated with an Audit.
for each server audit specification, you can use events to be audited like "backup/restore", "login success, login failure, db schema change, etc.

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20110407-190608]
FOR SERVER AUDIT [Audit-20110407-190438]
ADD (BACKUP_RESTORE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

3) You can also enable DB audit specifications like "delete, insert, update, dbcc" etc.
so its like whenever user Test inserts some data in a particular table, it will be logged in the audit file


CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20110407-192130]
FOR SERVER AUDIT [Audit-20110407-190438]
ADD (UPDATE ON OBJECT::[RetailSales].[CustomerRetailerXRef] BY [db_owner]),
ADD (DELETE ON OBJECT::[dbo].[Profiles] BY [db_owner])
WITH (STATE = ON)
GO
I've created all these using SSMS GUI but you can do using scripts as well like mentioned above.
this ofcourse fills up your audit log file, so make sure you drive has enough space.

You can query your audit file using SSMS or from the below queries:

SELECT * FROM sys.fn_get_audit_file
 ('w:\Audit\Audit-20110407-190438_824F0CB7-328A-4875-89AA-DCF42BA7BCEB_0_129467019443560000.sqlaudit',default,default)

You can get your audit file information using the below query
select * from sys.server_audits
select * from sys.server_audit_specifications

 select * from sys.database_audit_specifications

One more function to get the Audit info from DMVs

select * from sys.dm_audit_actions

index usage and physical stats


select * from sys.dm_db_index_usage_stats where object_id=1779185784 will give you index usage stats like user seeks, scans, laster user seek , last lookup, last system scan etc.
this will help you in identifying which of the indexes in the table are in use since your last sql server restart.

select * from sys.dm_db_index_physical_stats(5,1779185784,1,1,'detailed')
                                                                (dbid,obj id, index id, partition no,options)
options - detailed, sample,limited,null

This will give you the fragmentation percentage much like dbcc showcontig, using this info we can identify if we need to rebuild that particular indexe

select * from sys.dm_db_index_operational_stats(5,1779185784,null,null)
                                                                     (dbid,obj id, indx id, partion no)

This will give details like leaf delete count, leaf insert count, leaf update count, page lock count, row lock count etc.
using this we can identify how many times inserts,updates and deletes happened for this table and how many different types of locks have been acquired on these indexes.

Tuesday, April 5, 2011

Missing Indexes

Have gathered some info about missing indexes.

there are limitations like the below:

1) it doesn't specify order for the columns to be created if you want to create the missing index
2) it can't gather statistics for more than 500 missing index groups
3) doesn't give accurate statistics for inequality predicates
eg. for queries such as this
select * from table1 where col1>33

Now here is how to analyze the missing index DMVs and create indexes for those.

Gather the missing indexes using the query:

select * from sys.dm_db_missing_index_details where database_id=5 and object_id=1779185784

gather the missing index index_handle

select * from sys.dm_db_missing_index_groups where index_handle in
(
select index_handle from sys.dm_db_missing_index_details where database_id=5 and object_id=1779185784
)

you can see the columns in the first query but can get for each index handle group using the below query

select * from sys.dm_db_missing_index_columns(pass the index_handle)

create indexes based on the missing indexes like this

first mention the indexes under "equality" predicate, then under "inequality" and then finally include the columns under "include" category.