Solved

SQL Server 2005 - Locks

Posted on 2006-11-15
8
830 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:novknow
ID: 17945903
Would appreciate if you could provide some sample code on how to achieve it?
regards
0
 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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