Tuesday, October 5, 2010

SQL Facts: Indexes Limit Increased in SQL 2008
The limit of nonclustered indexes are increased from 249 to 999 in SQL Server 2008

Tuesday, September 21, 2010

SQL Administration: Dynamic Management Views


The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMVs are designed to be used instead of system tables and various other functions provided in SQL Server 2000. Here, we will see only about the frequently used DMVs.
Two types of dynamic management views:
a)   Server-scoped DMV: Stored in Master Database
b)   Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]

To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.
If you want to deny a user permission to query certain DMVs, you can use the DENY command to restrict access to a specific DMV.

Getting Started

All the DMVs exits in SYS schema and their names start with DM_. So when you need to query a DMV, you should prefix the view name with SYS. As an example, if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb 
In this article, I will be explaining some of the DMVs which can be used frequently to understand the current behavior of SQL Server:
  1. SQL Server related [Hardware Resources] DMV
  2. Database related DMV
  3. Index related DMV
  4. Execution related DMV

1.   SQL Server Related DMV

This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.
This section covers DMVs related to OS, Disk and Memory.

a)   sys.dm_os_sys_info

b)   sys.dm_os_hosts

c)   sys.dm_os_schedulers

d)   sys.dm_io_pending_io_requests

e)   sys.dm_io_virtual_file_stats

f)    sys.dm_os_memory_clerks

g)   sys.dm_os_ring_buffers

a)   sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.
This view returns information like the following:
  1. CPU Count: Number of logical CPUs in the server
  2. Hyperthread-ratio: Ratio of logical and physical CPUs
  3. Physical_memory_in_bytes: Amount of physical memory available
  4. Virtual_memory_in_bytes: Amount of virtual memory available
  5. Bpool_commited: Committed physical memory in buffer pool
  6. OS_Priority_class: Priority class for SQL Server process
  7. Max_workers_thread: Maximum number of workers which can be created

b)   sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.
  1. Name: Name of the host registered
  2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
  3. Active_tasks_count: Number active tasks host placed
  4. Active_ios_count: I/O requests from host waiting

c)   sys.dm_os_schedulers

SQL Administration: Database and Execution Related DMVs:

sys.database_permissions: Contains information about all the permissions held by users and roles

sys.database_permissions: Contains information about all the permissions held by users and roles

sys.columns: Contains data about each column of an object that has columns, such as views or tables

sys.database_principals: Contains information about all database users and database roles

sys.index_columns: -- Index columns

List of Execution DMVs:

References: MSDN


Friday, August 27, 2010

SQL Programming: Calling the web service with parameterized query string (using OLE automation)

Say, we have a webservice which sends SMS, which should be called by passing the needed parameters thru SQL query. 

We can get use of OLE Automation Stored Procedures within SQL Server.
List of Stored Procedure we can use to achieve this:
sp_OACreate - Creates an instance of an OLE object

- Calls a method of an OLE object.

- Destroys a created OLE object

- Sets a property of an OLE object to a new value

- Gets a property value of an OLE object.

- Stops the server-wide OLE Automation stored procedure execution environment


These stored procedures were disabled by default, and should be initiated manually from surface area configuration tool (Refer MSDN).administrator privileges were required to execute this stored procedures

In SQL 2008,there is no surface area configuration tool. It is integrated within the Facets option.(refer mssqltips)

Set these options to True for better execution.
  • ClrIntegrationEnabled
  • OleAutomationEnabled
  • XPCmdShellEnabled


Sample Stored Procedure Query:


declare @sUrl varchar(4000),@param1 varchar(100),@param2 varchar(100),
@obj int,@response varchar(800),@MobileNo varchar(50),@MovieName varchar(50), @SmsId int


select @RowCount =
from tbl_SMSAlertMovie where status is null

while (@RowCount > 0)
top 1 @SmsId =SmsId, MobileNo=MobileNo,@MovieName=MovieName from tbl_SMSAlertMovie where status is null

set @sUrl =
set @param1='&MobileNo='+@MobileNo
set @param2='&Movie='+@MovieName
set @sUrl= @sUrl+@param1+@param2
'WinHttp.WinHttpRequest.5.1', @obj OUT
'GET', @sUrl, false
'responseText', @response OUT
--    select @response [response] à use if you need to store result

update dbo.tbl_ SMSAlertMovie
set Status='Y' where SmsId=@SmsId

select @RowCount =
count(*)from tbl_SMSAlertMovie where status is null