long query

My SQL server blocking for a while
After monitoring with SQL Profiler I found a lot of interesting situation:

query duration is 2045607 msec (=34 min)
CPU usage is 0 msec, number of reads is 3 and number of writes is 0

Where could be a bottleneck ?

Query is tooooooooo long but resorces usage seems to be OK.

Help... SOS ...What about locking?|||Originally posted by snail
What about locking?

It looks with no locks|||Post the query, please.

blindman|||Originally posted by blindman
Post the query, please.

blindman

This is one of many querys with long response

SELECT PPKZIP ,POPZIP ,Zupanija FROM AVEZIP ORDER BY PPKZIP ASC|||Originally posted by valentini
This is one of many querys with long response

SELECT PPKZIP ,POPZIP ,Zupanija FROM AVEZIP ORDER BY PPKZIP ASC

Nr of rows ?
DDL of the table ?
Any index ?|||Originally posted by fadace
Nr of rows ?
DDL of the table ?
Any index ?

Rows about 300
Index on first field - PPKZIP
Table with 3 fields|||Are you seriously saying that it is taking 34 minutes for SQL server to sort 300 rows on an indexed column?

Are there any other processes running? Check the database activity and check the status of any scheduled jobs.

How many users are on the system while this is happening?|||Originally posted by blindman
Are you seriously saying that it is taking 34 minutes for SQL server to sort 300 rows on an indexed column?

Are there any other processes running? Check the database activity and check the status of any scheduled jobs.

How many users are on the system while this is happening?
No scheduling jobs and no special database activity.
As you can see CPU is not used (0 msec)
But after 30 min SQL response with packet of data
Possible from cached memory.

But after 30 min ..............
Is there maybe problem in conlfict with some other transaction
problem with TCP/IP or with physical RAM

...|||What happens if you run the same statement directly from Query Analyzer on the database server?

logins/sec

I'm monitoring 2005 while running our application by looking at

sys.dm_os_performance_counters, general statistics, logins/sec.

I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?

Hi,

This counter is indeed cumulative in the dmv.

I would check out the .NET application if it is creating 11000+ logins in 30 minutes.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Try to log the logins on the server using the logging functionality on the server, logging the failed as well as the successfull logins.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

in looking at the event viewer, there are hardly any logon/logout events.

Logging all logins.

?

|||

Here's what I'm seeing over and over in my trace. Our stored procedure should be writing a record each time, but it doesn't come accross in the trace as anything but a read. I have verified that the data is actually getting written to the table.

All spid 62.

All application name = .Net SqlClient Data Provider

EventClass textdata reads writes

Audit Logout NULL 68 4
RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<<our stored procedure>>> 2 0
Audit Logout NULL 70 4

RPC:Completed exec sp_reset_connection 0 0
Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed NULL NULL

RPC:Completed exec <<our stored procedure>> 2 0

Audit Logout NULL 72 4

|||Did you try to implement something like an audit mechanism, to track the written information or execution information in a table ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||not sure what you mean...some kind of trigger?|||Yep.

Jens K. Suessmeyer.

http://www.sqlserver2005.de