Solved

SQL Server 2005 - Locks

Posted on 2006-11-15
8
825 Views
Last Modified: 2012-06-27
Hi expert,
I am going through my application error log files (there a about 200 of them) daily.
For the past one week, I am seeing this error message:
"EOleException - Timeout expired"
and there are about 10 cases each day.

Can I conclude that these are due to insufficient locks in the SQL Server 2005 and need increase number of available locks?

My current configuration is:
name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
locks                                      5000  2147483647            0           0
1>

Or it could be due to other reasons?
Can you advice me?

regards
0
Comment
Question by:novknow
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17945746
No - you need to look at SQL to see if there actually are locks occurring (locks are actually common in SQL - you really need to look at blocking).

Are there specific times when you see this occurring? Try using sp_who2 and profilder to trace long running transactions and locking and blocking transactions first.
0
 

Author Comment

by:novknow
ID: 17945836
Hi Nightman,
I can't find any pattern, it happens to any client stations at any time.
Also, most of the update/insert statement are pretty fast.
I will try profiler tomorrow.
What useful infomation can I get from sp_who2? Is there a way to customise the output - the listing is very long?
regards
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17945876
You would have to write your own variant of it, or insert exec into a temp table and then conditionally select from that.
0
 

Author Comment

by:novknow
ID: 17945903
Would appreciate if you could provide some sample code on how to achieve it?
regards
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 29

Expert Comment

by:Nightman
ID: 17945905
For example (I never quite finished this):

CREATE PROCEDURE sp_who2_SortByDB  --- 2006/01/23 15:08
    @loginame     sysname = NULL,
    @CommandName  nvarchar(100)=NULL,
    @ProgramName  nvarchar(100)=NULL,
    @DBName       nvarchar(100)=NULL,
    @Block        int=NULL,
    @Sleep        int=NULL
as

set nocount on

declare
    @retcode         int

declare
    @sidlow         varbinary(85)
   ,@sidhigh        varbinary(85)
   ,@sid1           varbinary(85)
   ,@spidlow         int
   ,@spidhigh        int

declare
    @charMaxLenLoginName      varchar(6)
   ,@charMaxLenDBName         varchar(6)
   ,@charMaxLenCPUTime        varchar(10)
   ,@charMaxLenDiskIO         varchar(10)
   ,@charMaxLenHostName       varchar(10)
   ,@charMaxLenProgramName    varchar(10)
   ,@charMaxLenLastBatch      varchar(10)
   ,@charMaxLenCommand        varchar(10)

declare
    @charsidlow              varchar(85)
   ,@charsidhigh             varchar(85)
   ,@charspidlow              varchar(11)
   ,@charspidhigh             varchar(11)

--------

select
    @retcode         = 0      -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
    @spidlow         = 0
   ,@spidhigh        = 32767

--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

--------

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
      select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   select @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (lower(@loginame) IN ('active'))  --Special action, not sleeping.
   begin
   select @loginame = lower(@loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   select
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


--------------------  Capture consistent sysprocesses.  -------------------

SELECT

  spid
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname
 ,spid as 'spid_sort'

 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,last_batch,113) ,13 ,8 )
       as 'last_batch_char'

      INTO    #tb1_sysprocesses
      from master.dbo.sysprocesses   (nolock)



--------Screen out any rows?

IF (@loginame IN ('active'))
   DELETE #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    IN (
                     'AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'
                                  )

         and     blocked       = 0



--------Prepare to dynamically optimize column widths.


Select
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow     = convert(varchar,@spidlow)
   ,@charspidhigh    = convert(varchar,@spidhigh)



SELECT
             @charMaxLenLoginName =
                  convert( varchar
                          ,isnull( max( datalength(loginname)) ,5)
                         )

            ,@charMaxLenDBName    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
                         )

            ,@charMaxLenCPUTime   =
                  convert( varchar
                        ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
                         )

            ,@charMaxLenDiskIO    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
                         )

            ,@charMaxLenCommand  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
                         )

            ,@charMaxLenHostName  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
                         )

            ,@charMaxLenProgramName =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
                         )

            ,@charMaxLenLastBatch =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
                         )
      from
             #tb1_sysprocesses
      where
--             sid >= @sidlow
--      and    sid <= @sidhigh
--      and
             spid >= @spidlow
      and    spid <= @spidhigh



--------Output the report.

declare @SQLOutput nvarchar(4000)
set @SQLOutput='
SET nocount off

SELECT
             SPID          = convert(char(5),spid)

            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END

            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')

            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END

            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END

            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')

            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where
             spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh
      if @Block=1
        begin
          set @SQLOutput=@SQLOutput+ ' AND spid IN (SELECT blocked FROM #tb1_sysprocesses) OR blocked <>0  order by DBName,isnull(convert(char(5),blocked),''0'')'
          set @SQLOutput=@SQLOutput+ ' SET nocount on'
        end
      else
        begin
          IF @Sleep=1
          begin
            set @SQLOutput=@SQLOutput+ 'AND lower(status)<>''sleeping'''
          end
          IF @DBName IS NOT NULL
          begin
            set @SQLOutput=@SQLOutput+ 'AND db_name(dbid) = ''' + @DBName + ''''
          end
          IF @CommandName IS NOT NULL
          begin
            set @SQLOutput=@SQLOutput+ 'AND cmd = ''' + @CommandName + ''''
          end
          IF @ProgramName IS NOT NULL
          begin
            set @SQLOutput=@SQLOutput+ 'AND program_name LIKE ''%' + @ProgramName + '%'''
          end
                          set @SQLOutput=@SQLOutput+ ' order by DBName'
              
         
          if @CommandName IS NOT NULL
          begin
            set @SQLOutput=@SQLOutput+ ',cmd'
          end
          if @ProgramName IS NOT NULL
          begin
            set @SQLOutput=@SQLOutput+ ',program_name'
          end
          set @SQLOutput=@SQLOutput+ ' SET nocount on'
        end    
exec (@SQLOutput)
--EXECUTE(
--
--)
/*****AKUNDONE: removed from where-clause in above EXEC sqlstr
             sid >= ' + @charsidlow  + '
      and    sid <= ' + @charsidhigh + '
    and
**************/


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

return @retcode -- sp_who2

GO

To see locking and blocking processes:
exec sp_who2_SortByDB NULL,NULL,NULL,NULL,1

to see connections to a specific database (e.g. master):
exec sp_who2_SortByDB NULL,NULL,NULL,'master'

To see a particular application (e.g. query analyser):
exec sp_who2_SortByDB NULL,NULL,'SQL Query Analyzer',NULL

To see a particular command (e.g. Delete):
exec sp_who2_SortByDB NULL,'DELETE',NULL,NULL

This helps to narrow things down a little ;)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17945972
And if I forgot to mention it, you create that stored procedure in the master database.
0
 

Author Comment

by:novknow
ID: 17945976
Thanks, let me go through and get back to you.
regards
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17946009
Don't forget that you queries could also be timing out due to poor indexing and poor query design. You need to take that into account as well.

If you have queries where you aren't particularly concerned whether the data is 100% accurate (e.g. only reading committed transactions) you can always use table hints in your query.

e.g. SELECT * FROM MyTable WITH(NOLOCK).

This will do the following:
1. NOT lock the rows during each read.
2. Read uncommitted transaction data (i.e. transactions in process) without waiting for them to complete (will also read data that may be about to be rolled back as well!)

This is also known as a dirty read.

Cheers
Night
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now