How Windows Audio Works :- Direct Sound and WASAPI

Windows is the most widely used operating system in the world . people use it for verities of task  and listening audio is one of them . Microsoft windows 7 and above can render sound in two ways . using Directx’s direct sound support and using WASAPI support. most of the windows applications use Directsound approach . windows can play multiple stream from different applications when its using direct sound . Directsound works as a middle layer between application software and sound drivers . It takes sound from different application software and then resample all sound stream to one stream and then sends it to audio drivers .  the downside of this approach is that , we never get what we actually play . for e.g.. if some one is playing a sound file at 192 Khz, 24 bit . then windows will probably resample it into 44kHz, 16 Bit with other sound on machine. but on the plus side it provide a unified framework to developers to create application that can utilize windows audio without directly programming sound card. Direct sound automatically resample sound stream to matching sample rate that can be supported by audio hardware.

  On the other hand , windows 7 also provide WASAPI mode. by using WASAPI mode , application can ask for  exclusive access of sound card and can directly send stream to Sound card . In this case, application have to handle all the re-sampling if the sample rate of played audio is not supported by sound card . WASAPI provide two modes to work with . shared mode and exclusive mode . shared mode works same as direct sound and windows audio mixer do the re-sampling and mixing . in exclusive mode  WASAPI bypass windows audio mixer and application can directly send data to sound card. in this mode, application can decode the formats like DTS,DTS master audio , DOLBY True HD, Dolby digital, Flac etc and send the unaltered stream to audio card.

Also addition to different modes , its also important that how WASAPI manage data between buffers . WASAPI use PUSH and PULL approach to ask for data which needs to be processed. in push approach application pushes the data to buffers and continuously monitor it and as soon as it sees it empted out , it fill it again . in PULL approach, which is a modern technique, application use two buffers . the audio divers gives a call to application as soon as it sees the empty buffer and it start using data from other buffer . in meantime applications fills the empty buffer.

  many application software support WASAPI and DirectX. XBMC, Foober2000 and Winamp (with plugin only) are few popular options if someone wants to give it a try … 🙂

Best Audio Format

Today, there are so many audio format that it become difficult job to consider one over others. people often ask me , which is the best audio format to archive CD’s and keeping digital audio. and the answer is not so straightforward. audio codec’s can be divide into two types, lossy and lossless . lossy means the codec use algorithms which eliminate frequencies which is above and below 20-2000 Hz and hence reduce the file size. on the other hand lossless codec compresses the audio just like zip format . one can achieve bit-perfact audio back from lossless codec .

    i  consider MP3 as best Lossy Audio format simply because of  support by different hardware and software players. MP3 is quite old format and we already have new format in market such as AAC, WMA etc. AAC is consider to be better then MP3 at same bit rate but its not as widely supported as MP3.

On lossless side , FLAC can be consider as best audio codec as its free and can be played on most of the software audio players. FLAC can also support multi channel audio just like Dolby true HD or DTS master audio.

I love to hear from others , what they think ……..

Reading SQL Server Error Log Through TSQL Statement

One of the issue which we face while doing troubleshooting, is to read error logs of SQL server. one should have to go through GUI elements of SSMS to read the error logs . but it would be much more easier if one can read error logs through TSQL command as it provide more flexibility to filter out the information. xp_readerrrorlog is undocumented extended stored procedure which do just the same . it reads error log without going through GUI .

It takes four parameter to execute

• Parameter 1
  Type :- INT
  Name :- Error Log File
  Default Value  :- 0
  Description  :- 0 for current, 1 for Archive #1, 2 for Archive #2, and so on
• Parameter 2
  Type :- INT
  Name :- Log File Type
  Default Value :- 1
  Description :- 1 for error log and 2 for SQL Server Agent log
• Parameter 3
  Type :- Varchar (255)
  Name :- Search String 1
  Description :- String one wants to search in error log
• Parameter 2
  Type :- varchar (255)
  Name :- Search String 2
  Description :- Its second string which can be searched to refine the search result

 

The above example search for “2005” and “exec” in current error log file for SQL server instance.

Finding Port Number of SQL Instance

There are times when one needs a port number for SQL Server instance . finding port number through automated script is not that simple as the environment may have named instance and default instances. both types need different way to fetch out port number from registry key . i have wrote a script which help to automate this task. the script first finds the type of instance , whether it is default or named and then query respective keys to get the port number …

 

DECLARE @tcpport nvarchar(5)
DECLARE @insname  nvarchar(50)
DECLARE @MyKey  nvarchar(254)
if CHARINDEX(‘\’,@@servername) > 0
begin
set @insname = substring(@@servername,CHARINDEX(‘\’,@@servername)+ 1,len(@@servername))
set @MyKey = ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\’ + @insname + ‘\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
else
begin
set @MyKey = ‘SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\SUPERSOCKETNETLIB\TCP’
end
EXEC xp_regread
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
@key        =    @MyKey,
@value_name    =    ‘TcpPort’,
@value        =    @tcpport OUTPUT

Select @@servername as ServerName ,@tcpport as PortNumber

 

The query works fine with both Sql Server 2008 and 2005 instances

Finding Database Status Through SQL Server Agent Job

Finding database status always remains a important job for DBA’s , as the primary duty for DBA is to look after the health of database . database can go into suspect or recovery mode from verity of reasons and most common ones are disk problem , I/O issues and consistency checks . one can query sys.databases system view and look for state column to find the status of the database. the state is a tinynt column and shows state through numbers , which are described below

0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE

or, one can use state_desc column to find the state of database in plain English. this could show one of the following entries

ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

the following query will give complete status of database’s health on server.

select name,state,state_desc from sys.databases

also if someone wants to automate this whole procedure and wants notifications about database health in case of any problem , then one can use this script to create a SQL Server agent job , which will notify user in case of any issue. the schedule can be set to every ten minutes

if exists(SELECT [Name] as [Database Name],state_desc as [Database State] FROM sys.databases
where state_desc not like ‘ONLINE’ and state_desc not like ‘RESTORING’)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
    @recipients=N’dba_address@domain_name.com’,
       @subject = ‘SQL Server Database Status’,
    @Query = ‘SELECT cast([Name] as nvarchar(50)) as [Database Name],cast([state_desc] as nvarchar(20)) as [Database State] FROM sys.databases
where state_desc not like ”ONLINE”’;
END

Useful Links

http://msdn.microsoft.com/en-us/library/ms178534.aspx

Finding Row Count of All Tables in SQL Server

There are times when one need to find the row count of all the tables in the database. there are many ways , one can use cursor to loop through table name and store result in temp table and then display it all . but by using undocumented stored procedure sp_msForEachTable , its become very easy to find desired result .

CREATE TABLE #Tabdata
    (
        Tablename NVARCHAR(150),
        RecordCount INT
    )

INSERT #Tabdata
EXEC sp_msForEachTable ‘SELECT ”?” as Tablename,COUNT(*) as count FROM ?’

Select Tablename,RecordCount from #tabdata order by RecordCount desc
drop table #Tabdata

Cursor free Way to kill Database Connections



Killing all connection to SQL Server database is bit tricky task. One can write script which loops through all the user connection and then kill every connection. Other way is to open activity monitor and then sort it on database name and then try to kill all the connections to database. Both the methods are very much capable of doing what we need but theses methods are time consuming. Enlisted script also does the same, but in more optimize way.

declare @dbname nvarchar(100)
declare @KillStat nvarchar(4000)
set @KillStat = ”
set @dbname = ‘dbname’
select  @KillStat = @KillStat + ‘Kill ‘ + cast(spid as nvarchar) + char(13)
 from sysprocesses where dbid = db_id(@dbname)
Print @KillStat
exec(@KillStat)
Here, the script use set based approach to create the query which can kill all the user connection and then it executes it by using exec command. It also prints all the spid’s of user connection which it is killing.

The only parameter which has to pass is @dbname. It contain the name of source database

SQL Server Client Tools licensing

Hello all, I’ve been trying to find out some licensing info concerning the client tools and SSMS, but can’t find anything concrete. But after some googling I come to know that, if a user or a device has the legal right (also known as the license) to access a SQL-Server than it is legal to install client tools in order to give access to it and manage it.  Of course, but only the users with a valid license will have the right to use these tools. There is no special licensing to take into consideration for installing the client tools other than to have the right to access a SQL-server instance.  e.g if this SQL-Server is operating under the Per Processor mode, everyone and every device in your organization have the right to install the client tools in order to access and manage this SQL-Server.  Of course, this doesn’t give the right to use these tools to access *another* SQL-Server for which a particular user/device wouldn’t have the right to access it in the first place.