Re: SQL Timeout problem
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 01/17/05
- Next message: David Portas: "Re: date comparision"
- Previous message: Stuart Fish: "Re: How to import fixed-width text file in MS SQL 2000?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 Jan 2005 13:56:01 -0600
With no disrespect to Chris, his suggestions regardig locking and blocking
timeouts are on the money; however, here is what I've found to be a problem,
often, when dealing with multiple high transaction websites and,
particularly, XML.
First, each connection object within SQL Server takes some memory, outside
of the normal buffer pool, where there is often little flexibility in the
amount available.
Check two things, the amount of buffer pool memory allocated to SQL Server
and the amount of freely usable MEM TO LEAVE memory to use for connections.
Next, make sure that all of the current connections are, or have been,
active recently. I'll let you define what recently means but IIS/ASP has a
nasty habit of orphaning database connections that induces memory leaks both
into the web server(s) as well as the SQL Server installation. There is a
finite amount of concurrent connections possible on the order of 32,635, but
usually is less than this due to insufficient DBMS ram. Look at the last
batch time and kill any that haven't done anything for awhile, certainly
nothing in the last 24 hours.
The next issue concerns the resource requirements for process XML, either
inbound or outbound. XML parsing and creation is a tedious, resouce
consumptive process and you will see your CPU usage and Memory usage climb
because of it. Moreover, XML is larger than native TDS packets, by a factor
of three to five over the native data transfer. This too bears down on CPU
and Memory usage to navigate the TCP/IP stack processing packets. I would
avoid the usage on the DBMS and leave that to the Application servers or the
clients.
However, if you must, then insure sufficient memory and CPU on the DBMS
host. I would begin with a thorough examiniation of the SQL Server perfmon
metrics with respect to this to determine capacity.
Hope this helps.
Sincerely,
Anthony Thomas
--
"Matt Relf" <flibble@off.com> wrote in message
news:uPtZ2bP4EHA.2196@TK2MSFTNGP14.phx.gbl...
Sorry to beg, but could anyone please help with this. I really don't know
where to turn next.
Thanks,
Matt
> Hi Chris!
> We have a failure! And your scripts have returned results.
>
> The results of the first query returned.
> 245 261 9172 SELECT
> 247 261 19219 SELECT
> 249 261 12438 SELECT
> 251 261 12531 INSERT
> 253 261 21500 SELECT
> 256 261 15859 SELECT
> 258 261 7969 SELECT
> 259 261 21141 SELECT
> 260 261 20125 SELECT
> 261 226 24891 SELECT
>
> This list would change on repeated running of the script. Certain items
> would stay on the the list.
>
> The second bit of script you offered returned the same line for any of the
> persistently blocked spids.
> Sorry, but I've removed some items to protect our client.
> ------------------------------------------
> 261
> sleeping
> **user_name**
> **host_name**
> 262
> **db_name**
> SELECT
> 172
> 0
> 12/09 16:17:24
> Microsoft(R) Windows (R) 2000 Operating System
> 261
>
> ------------------------------------------
> and
> ------------------------------------------
> RPC
> Event
> 0
> spTODOrderStatusGet;1
>
> ------------------------------------------
>
> The results for the "BlkBy" spid is the same. The listing of this SP is
> below.
> ------------------------------------------
> CREATE PROCEDURE spTODOrderStatusGet
> @SessionID UNIQUEIDENTIFIER
> AS
>
> SELECT 1 as tag,
> null as parent,
> @SessionID as [OrderStatusGet!1!sessionID],
> OrderStatusID as [OrderStatusGet!1!OrderStatusID!element],
> OrderSubStatusID as [OrderStatusGet!1!OrderSubStatusID!element],
> ReferenceCode as [OrderStatusGet!1!ReferenceCode!element]
> FROM tblOrders
> WHERE SessionID = @SessionID
> FOR XML EXPLICIT
> GO
> ------------------------------------------
>
> Which I really can't see anything wrong with. Once again, HELP!
>
> Many Thanks in Advance,
> Matt
>
>
> "Chris" <Chris@discussions.microsoft.com> wrote in message
> news:73978FFC-5B29-436E-BA28-F7131C67A95E@microsoft.com...
> >
> > During the period you are experiencing slowness, analyze the server for
> any
> > locking/blocking spids.
> >
> > -- will return any blocked spid, the blocking spid (blocked), the
waittime
> > and command of the blocked spid
> > select spid, blocked, waittime, cmd from master.dbo.sysprocesses
(nolock)
> > where blocked <> 0
> > order by blocked desc, spid
> >
> > -- Who and what they are doing
> > sp_who2 1074
> > dbcc inputbuffer(1074)
> >
> > -- Locks acquired by blocking spid. The first 2 params will be different
> on
> > your server. I captured this undocumented sp by running a trace against
> SQL
> > Enterprise Manager and selecting Locks/Process ID under Current
Activity.
> If
> > you see TAB locks as a result - this is bad and is most likely a
> > table/clustered index scan, look at optimizing code to use indexes
> > sp_MSget_current_activity 801,4,@spid=72
> >
> > If you do not see any blocking spids you should look at system
resources.
> > How is CPU, RAM usage, is Paging occurring (need to run perm mon for
> this.)
> > Or Spotlight by Quest is a good tool for resource usage.
> >
> > Hope this helps.
> >
> > Chris
> > MCSE MCDBA OCP
> > MyDBA2000.com
> >
> > "Matt Relf" wrote:
> >
> > > Hi All,
> > > I have a problem with a VB Script ASP site using a 2000 SQL server.
This
> set
> > > up is a little complicated so I'll explain the setup before I explain
my
> > > problem.
> > > This is a ticket booking system which runs many different sites on
three
> > > different web servers (2 x 2000 Server and 1 x 2003 Server Standard).
At
> the
> > > core of this is a single SQL 2000 Server running on a 2000 Server box.
> On
> > > this runs three DBs; one DB is a store of core client information, the
> other
> > > two (A and B) are identical in structure but store ticket information
> > > specific to the end vendors.
> > >
> > > Webserver 1 - 2003 Server
> > > Runs a single ASP site which connects to the core DB.
> > >
> > > Webserver 2 - 2000 Server
> > > Runs multiple ASP sites which all connect to DB (A).
> > >
> > > Webserver 3 - 2000 Server
> > > Runs multiple ASP sites which all connect to DB (B).
> > >
> > > All sites connect to the DBs via an ODBC DSN. All DB interactions are
> via
> > > stored procedures.
> > >
> > > All stored procedures return an XML result. This may be simply the
> identity
> > > of a newly inserted item wrapped in tags, or may be a complicated
> structure
> > > of data.
> > >
> > > All the websites on Webservers 2 and 3 use identical source code with
> > > different styling applied.
> > >
> > > In addition to the sites are various VB6 applications which handle
> > > communication to credit card clearing services and also to the end
> ticket
> > > vendors. These applications also use ODBC DSN connections and XML
> results.
> > >
> > > The Problem!
> > > ---------------
> > > At what would appear to be random intervals (between 3 hours and 5
days)
> > > certain communications with the database fail. This initially affects
> the
> > > sites on Webserver 2 and the ticket vendor communication applications.
> If
> > > left unchecked the problem speads to Webserver 3 and also areas of the
> site
> > > on Webserver 1 (but not the entire site). This appears to not affect
the
> > > stored procedures which return very small amounts of XML (e.g. create
> > > session) but anything more will eventually become an SQL Timeout
error.
> > >
> > > We have found we can "fix" the problem by various methods; restart the
> > > affected webserver, restart the database server, restart the database
> > > service. The problem we have is that Nothing is logging an error.
Event
> > > viewer on all the servers show no events which would suggested that
> > > something was broken.
> > >
> > > Since the problem can be resolved by restarting either the webserver
of
> the
> > > database server or service my gut feeling is that the problem must lie
> in
> > > ODBC since this is the only part which "bridges" between servers. But
> I've
> > > checked the set up against best practice and the only thing which was
> not
> > > setup was connection pooling. But enabling this has had no impact on
the
> > > problem.
> > > Some of our older stored procedures use tempory tables (later ones use
> table
> > > variables) so I've checked through all of these to check tables are
> getting
> > > correctly dropped and all is well there too.
> > >
> > > I'm now stuck on what to try next. With no error message (other than
my
> > > sites and applications timing out) to tell me what is wrong I'm stuck.
> HELP!
> > >
> > > Many thanks in advance,
> > > Matt James
> > >
> > >
> > >
> > >
>
>
- Next message: David Portas: "Re: date comparision"
- Previous message: Stuart Fish: "Re: How to import fixed-width text file in MS SQL 2000?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
