Query Performance

I am attempting to get a better understanding of why my SQL 2005 setup when running a simple select statement on a large table is displaying very low IO in performance monitor. If i run a single Select * From testtable i see 4mb\sec transfer and Disk reads\sec is around 8-9. This particular table is sitting on a single U320 10k drive so i expecting to see far more substantial IO. Does anyone have any information on how IO is consumed using different SQL operations so i can obtain a better understanding?

Flush the cache. Some, and possibly a lot, of data may be cached in memory.

Also, since a select query does not require any transaction logging or updating of indexes or statistics, a select query would have substantially less IO than an insert, update, or delete query.

Flush the cache and then run: Insert Into testtable Select * From testtable Order By SomeNonClusteredField

|||

hi

I did flush the cache prior to running this statement. Why doent SQL use the I\O to its full potential if that is the only process running on the box? I was expecting to see the I\O near to saturated.

Query Not Collating correctly

I have restored an SQL 2000 database in to 2005 Standard edition.

I have several views that are displaying incorrect results though when viewed in SQL Management Studio, and hence back to Excel Word etc....

The view is as follows:

SELECT TOP (100) PERCENT DATEPART(mm, ARV_date) AS Month, COUNT(*) AS Count, SUM(DATEDIFF(mi, ARV_date, RES_date)) AS [Minutes Unresolved],
MAX(DATEDIFF(mi, ARV_date, RES_date)) AS [Max Dur(min)]
FROM dbo.ps_Remedy_Data
WHERE (DATEPART(yy, ARV_date) = DATEPART(yy, GETDATE())) AND (RMDY_Priority = 'Urgent')
GROUP BY DATEPART(mm, ARV_date), RMDY_Priority
ORDER BY Month

When I execute it from Word or Excel the returned data is NOT in Month order...

When I right click the view and say Open View it returns the same unsorted data...

When I right click the view and say MODIFY and do nothing else except EXECUTE the SQL the returned data is in the correct sequence.

This is extremely annoying and can only be described as a bug.

This is a new server running WIN 2003 SP1, with SQL Server 2005 SP1.

Thoughts appreciated, many thanks in advance

regards

Order in views make no sense, though the order by clause is only used within the use of the "TOP Something" clause to ensure that the data is ordered first to give the right data back, using TOP 100 Precent won′t have any effect on that. There is a new blog entry from Kimberly about that, perhaps you shoul have a look on that.

http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=79de45cb-2da6-4510-ae43-7e5e42e4c02e

The best way would be to treat the view as a column definition and do the order on the view.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de