aiDeeva.com

Category: SQL Server 2008 R2

  • What is “Null”? How much space “Null” value takes in SQL Server?

    Null is neither zero nor empty string. Null is not a value at all. Most importantly for our discussion, one null value does not equal any other null value. In the RDBMS, Null simply means a value that is not known.

    NULL value can occupy in the database based on the coulumn data type and width.

    Fixed length data type NULL value takes the space as width of filed. (Char (5) – NULL value take 5 bytes)
    Variable length data type NULL value takes 2 bytes. (varChar (5) – NULL value take 2 bytes)
    Integer data type null value takes 4 bytes space

    You can use the sparse columns to save the space of NULL values. http://technet.microsoft.com/en-us/library/cc280604.aspx

  • Script to find out Stored Procedures which are Using most resources

    While doing the stored procedure performance tuning, you can use sys.dm_exec_procedure_stats DMV to get resource intensive procedures.

    You can use the below script for it.

    SELECT DB_NAME(database_id) AS DatabaseName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS Average_CPU
      ,total_elapsed_time / execution_count AS Average_Elapsed_Time
      ,total_logical_reads / execution_count AS Average_Logical_Reads
      ,total_logical_writes / execution_count AS Average_Logical_Writes
      ,total_physical_reads  / execution_count AS Average_Physical_Reads
FROM sys.dm_exec_procedure_stats 
where database_id <> 32767
ORDER BY Average_Logical_Reads DESC

  • T-SQL Script to find out the database file size, space used and available free space

    While troubleshooting disk space issue, it is essential to know about the database file size statistics. You can execute below script to get database file size information.

    set nocount on

create table #dbfileInfo(
name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))

declare @mySQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR

SET @cur_DBName = CURSOR FOR
select name from sys.databases

OPEN @cur_DBName
FETCH NEXT
FROM @cur_DBName INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName
if DATABASEPROPERTYEX(@dbName, 'status') = 'ONLINE'
begin
select @mySQL = 
    '
        use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a
    '
    exec sp_executesql @mySQL
end
FETCH NEXT
FROM @cur_DBName INTO @dbName

END
CLOSE @cur_DBName
DEALLOCATE @cur_DBName
GO

select * from #dbfileInfo
drop table #dbfileInfo

    FileOutput

  • How to make Lite Speed Central database and Local repository in sync?

    If you have configured the LiteSpeed in your environment using the Lite Speed Central repository option, there will be a scenario when Lite Speed Central repository misses some data. To make the data available in central repository you can use the LiteSpeed local database and execute the below procedure to ensure that cenral repository comes in sync with the local repository.

    exec dbo.xp_replicate_activity_statistics

  • Permission required for SP_UpdateStats

    To update the statistics on the database you should either be DBO of that database or SysAdmin on SQL instance.
    SPUpdateStats

    If you want to grant the least permission instead of server level permission you make the user DBO of the database. You can do it from database properties windows as below.

    DBO

    You can also make the user DBO using below command.

    USE [My_Policy]
    GO
    EXEC dbo.sp_changedbowner @loginame = N’jugal’, @map = false
    GO