Re: Using Function Table ? using Sql Server 2008


Luqman (pearlsoft@xxxxxxxxxxxx) writes:
As I have no sound knowledge about performance and tuning of queries,
however I have heard that performance can be evaluated using Execution
Plan, is it true? if so, how ?

Hm, to evalutate performance as such, run the query with production-size
data like this:

SELECT @d = getdate()
<query>
SELECT datediff(ms, @d, getdate())

If the time returned is satisfactory, you don't really have to do more.

It is when the execution time is not to your like when it's time to
look at the query plan, to get an idea of what the problem might be.

I should hasten to add that I am simplifying things a bit here. Even
if the execution time seems to be OK, it may still be worth to look at
the query plan. This may reveal a scan that could be avoided and that
it's possible to get the query running even faster.

Another very important thing here is caching. If you run the same
query twice, you may find that it executes much faster the second
time. This is because the first time, the data was on disk, whereas
teh second time it was in cache. This is particularly important if
you want to compare two queries for the same problem.





--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)
  • Re: SQL Server Query Analyzer faster than ADO.Net SQL Data Provider
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... > when compiling a plan. ... > execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.clients)
  • Re: Query Statment is Fast But Stored Procedure is Slow
    ... poor plan the first time it is run. ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
Loading