Posting the details of this situation so that others (including Microsoft)
are aware...
We had a simple query such as this on a multi-processor SQL Server:
SELECT count(*) FROM [Northwind].[dbo].[Orders] WHERE ShipRegion IS NULL
That would generate various severe errors (3624, 5180, 823), some of which
at first appeared to be hardware related. The errors were not generated if
the query were run with OPTION (MAXDOP 1). In the end a DBCC REINDEX of a
particular index on the table involved in the query fixed the problem,
although DBCC checks never indicated any problem with indexes.
Symptoms:
--
1. A stack dump indicating a failed assertion in file recbase.cpp
2. One of the following before the stack dump:
A. Error 3624 (retail assertion) of severity 20 (fatal error in current
process) that has follow-up message indicating the cause of the error, i.e.,
just "Error: 3624, Severity: 20, State: 1".
B. Error 5180 (could not open FCB for invalid file) of severity 22 (fatal
error, table integrity suspect) such as the following: "Error: 5180,
Severity: 22, State: 1 <next log entry> Could not open FCB for invalid file
ID 768 in database 'Northwind' ".
C. Error 823 (I/O error) of severity 24 (hardware error) such as the
following: "Error: 823, Severity: 24, State: 2 <new log entry> I/O error
38(Reached the end of the file.) detected during read at offset
0x00002000600000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Northwind.MDF' ".
3. The offending command that generated the initial failed assertion
continues to throw one of three errors listed above on subsequent
executions.
4. There are no hardware-related events in the Windows/NT Event Log.
5. The offending command uses parallelism.
6. When the offending command is run with OPTION (MAXDOP 1) to force serial
execution, it does not throw an error.
7. The following commands do not detect any integrity errors: DBCC
CHECKDB, DBCC CHECKCATALOG, DBCC CHECKTABLE (table only or table plus index
arguments), and DBCC CHECKFILEGROUP.
Investigation Notes:
--
1. Issue came up with the following statement sql statement
SELECT count(*) FROM [Northwind].[dbo].[Orders] WHERE ShipRegion IS NULL
2. Adding OPTION (MAXDOP 1) brought back results
3. Adding WITH (NOLOCK) had no effect.
4. Individual column names were substituted for the * in the SELECT clause.
The results were mixed: some executions brought back results and others
returned one of the errors listed in the Symptoms above. Checking the
execution plans for all the variants using the MAXDOP option (so that the
actual plan could be retrieved, not just the planned one) revealed that the
failing ones all used a particular nonclustered index while the successful
ones used the clustered index.
5. When running the DBCC CHECK commands listed in the symptoms to try to
rectify the issue, the filegroup and indid of the problem index where used
where possible (in addition to the more generic executions with just a table
name). No DBCC commands revealed any integrity errors, though, regardless
of whether they used the table name or the table name plus the
filegroup/index.
Resolution:
--
DBCC REINDEX of the problematic index resolved the issue without
serialization of the query plan (MAXDOP 1). Once working, the actual
execution plan was checked to ensure it used parallelism; it did.
Subsequent tests with OPTION (MAXDOP 1) continued to work, as did the
inclusion/omission of locking hints like (NOLOCK).Hi Frank,
Thans for sharing your experience with MSDN Newsgroup!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Showing posts with label details. Show all posts
Showing posts with label details. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
query from 3 tables
hi,
I have 3 tables
[Credit Memo - Expense Details] - which i refer to CMED
[Credit Memo - Project Change Request] - which i refer to CMPCR
[Credit Memo Table] - which i refer to CM
CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]
CMED and CMPCR are not related
My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )
So if there is only a match with CMPCR the CMED fields should be blank.
I am thinking of a union but just couldn't get it.
Below is the code to get all the records where CM invoice matches AND pcr matches
i need it to be OR
SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];
MS access 2003 - i hate it but i jsut came on board and this is already setup/
Thankshow about to use outer join?
SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.|||you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.
by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.|||i disagree, madafaka
peter's union suggestion works perfectly, you should try it
it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins|||maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.|||why? because it might be way faster, that's why :)|||why? because it might be way faster, that's why :)
Now you're wrong. The performance is the reason I avoid using UNION.
"When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."
"Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value
"This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"
SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value|||dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN|||ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.|||Just a reply to some of the topics mentioned in previous posts:
- Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).
- There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...
- Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.
- The equivalent of the querySELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2where a.c2 is a foreign key and b.c2 is the corresponding primary key, isSELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
UNION ALL
SELECT a.c1, a.c2, NULL, NULL
FROM tablea AS a WHERE a.c2 IS NULLWhen a.c2 is not a foreign key, the condition in the second query becomesWHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)
- A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.|||Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thankssql
I have 3 tables
[Credit Memo - Expense Details] - which i refer to CMED
[Credit Memo - Project Change Request] - which i refer to CMPCR
[Credit Memo Table] - which i refer to CM
CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]
CMED and CMPCR are not related
My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )
So if there is only a match with CMPCR the CMED fields should be blank.
I am thinking of a union but just couldn't get it.
Below is the code to get all the records where CM invoice matches AND pcr matches
i need it to be OR
SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];
MS access 2003 - i hate it but i jsut came on board and this is already setup/
Thankshow about to use outer join?
SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.|||you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.
by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.|||i disagree, madafaka
peter's union suggestion works perfectly, you should try it
it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins|||maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.|||why? because it might be way faster, that's why :)|||why? because it might be way faster, that's why :)
Now you're wrong. The performance is the reason I avoid using UNION.
"When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."
"Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value
"This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"
SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value|||dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN|||ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.|||Just a reply to some of the topics mentioned in previous posts:
- Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).
- There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...
- Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.
- The equivalent of the querySELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2where a.c2 is a foreign key and b.c2 is the corresponding primary key, isSELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
UNION ALL
SELECT a.c1, a.c2, NULL, NULL
FROM tablea AS a WHERE a.c2 IS NULLWhen a.c2 is not a foreign key, the condition in the second query becomesWHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)
- A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.|||Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thankssql
Tuesday, March 20, 2012
Query Execution Plan
Hi all
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
ThanksSee if this helps
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.examnotes.net> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"S
" operations are good and mean an index is used.
- Index S
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.examnotes.net> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
ThanksSee if this helps
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.examnotes.net> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"S
" operations are good and mean an index is used.- Index S
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.examnotes.net> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>
Query Execution Plan
Hi all
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
Thanks
See if this helps
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.codecomments.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>
|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com
|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.codecomments.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
Thanks
See if this helps
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.codecomments.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>
|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com
|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.codecomments.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
shows
> in
> any
>
Query Execution Plan
Hi all
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
ThanksSee if this helps
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.developersdex.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.developersdex.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> > Hi all
> > Can anyone provide me general description or link about the details
shows
> in
> > query execution plan.
> > How to use it to decide more better performance.
> > Which steps in exectuion plan are not good and need to improve. Is there
> any
> > general description on it?
> >
> >
> > Thanks
> >
> >
> >
> >
>
Can anyone provide me general description or link about the details shows in
query execution plan.
How to use it to decide more better performance.
Which steps in exectuion plan are not good and need to improve. Is there any
general description on it?
ThanksSee if this helps
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"AM" <anonymous@.developersdex.com> wrote in message
news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> Hi all
> Can anyone provide me general description or link about the details shows
in
> query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is there
any
> general description on it?
>
> Thanks
>
>|||AM wrote:
> Hi all
> Can anyone provide me general description or link about the details
> shows in query execution plan.
> How to use it to decide more better performance.
> Which steps in exectuion plan are not good and need to improve. Is
> there any general description on it?
>
> Thanks
BOL has some basic information. You might try reading Kalen Delaney's
book "Inside SQL Server 2000" or "SQL Server 2000 Performance Tuning".
Or check out SQLServerPerformance.com.
In general, "scan" operations are bad and mean an index is unavailable
or cannot be utilized for best performance.
- Table Scan - on a table without a clustered index (a heap)
- Clustered Index Scan - on a table with a clustered index (could be a
partial scan)
- Index Scan - just means an index is scanned (could be a partial scan)
"Seek" operations are good and mean an index is used.
- Index Seek
"Bookmark Lookup" operations are necessary for many queries that use
non-clustered indexes, but can add about 50% overhead to using the
index. The lookup is used to access column values in the table that are
not in the index. You can sometimes avoid bookmark lookup ops with a
well-designed clustered index or covering indexes.
"Sort" operations are... sometimes bad, sometimes necessary, but always
add overhead. If your sort ops are there because of an ORDER BY in the
query, remove it. Few applications require sorted data come back from
SQL Server and many developers leave them in because sorted results are
easier to debug during development.
David Gugick
Imceda Software
www.imceda.com|||Thanks all
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23GGILfeSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> See if this helps
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "AM" <anonymous@.developersdex.com> wrote in message
> news:eLldf1dSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> > Hi all
> > Can anyone provide me general description or link about the details
shows
> in
> > query execution plan.
> > How to use it to decide more better performance.
> > Which steps in exectuion plan are not good and need to improve. Is there
> any
> > general description on it?
> >
> >
> > Thanks
> >
> >
> >
> >
>
Subscribe to:
Posts (Atom)