Showing posts with label attempting. Show all posts
Showing posts with label attempting. Show all posts

Tuesday, March 20, 2012

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 paramaters using ODBC

I am attempting to add parameters to a query that is run via ODBC. When I use a "?" in my query to indicate a parameter, it gives me an error but seems to work ok, but if I change the name of the parameter it loses the connection between the query parameter and the report parameter. What is it that connects query parameters to report parameters? Also, since a query parameter is defined by a single "?" with no variable name, what do you do if you want to prompt once for a value, and then use it multiple times in a query?

Report parameters (defined at the report level) and query parameters are independent. By default, we will generate 1 report parameter for each query parameter we detect but the binding is up to you. If you go to the data tab and bring up the dataset properties (the elipsis beside the dataset drop down), you can see the binding. Since ODBC does not support named parameters, you need to make sure that the order of the parameters still maps to the correct report parameters if you change the query.