Author Archives: Jugal Shah

Unknown's avatar

About Jugal Shah

Jugal Shah has 19 plus years of experience in leading and managing the data and analytics practices. He has done the significant work in databases, analytics and generative AI projects. You can check his profile on http://sqldbpool.com/certificationawards/ URL.

How to install IIS on Windows 7?

IIS is mandatory for the Virtual Server installation, in this article I will guide you how you can install the IIS on windows 7 or windows vista.

 

Step 1: click Start, and then click Control Panel

 

 

Step 2: In the Control Panel, click Programs then Click Turn Windows features on or off and from Windows Features dialog box Select Internet Information Services to choose the default features for installation.

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