different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
DanHi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
(Please reply only to the newsgroup)

different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
DanHi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
--
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
--
(Please reply only to the newsgroup)

different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
Dan
Hi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
http://www.aspfaq.com/
(Reverse address to reply.)
|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
(Please reply only to the newsgroup)

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.
|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1

different kind of Indexes for different tables

Hi ,
I read in Oracle that each table's can have it's own kind of indexes e.g IOT
, BitMap , B-Tree for different purposes used by Application.
Is there such function(s)/options in SQL Server ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1Indexes in MS-SQL Server are in B-Tree structure.
But you can fine tune them for your needs with some options (like the
fill factor). take a look at the "CREATE INDEX" in the BOL.|||Hi ,
tks for ur advise
rdgs
amit.tzafrir@.gmail.com wrote:
>Indexes in MS-SQL Server are in B-Tree structure.
>But you can fine tune them for your needs with some options (like the
>fill factor). take a look at the "CREATE INDEX" in the BOL.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1

Different indexes - performance analysis question

Hello

I'm doing some performance analysis for my application. I'm doing 7600 SQL queries based on the following SQL query:

SELECT LocationId, ProductId, BatchId, SUM(Quantity) AS Quantity FROM Logistics WHERE UserId = [number] AND ProductId IN ([productidlist]) GROUP BY LocationId, ProductId, BatchId;

Data in table Logistics has LocationId = 1 and BatchId = 0 for absolute all rows in this test, UserId and ProductId may be different. For each SQL Query it's doing, it's also inserting new rows in the table. The table starts with 0 rows for the first SQL query above, ends with 35 000 rows. Execution for both Editions below is exactly the same (same data inserts)

The graph below is showing the time used in milliseconds (y axis) for each query (x axis) - both editions is doing the exactly the same query but with different indexes.

URL to graph: http://www.lostfields.com/sqlindexing.gif

Edition 2 has the following priority on the PK: UserId, LocationId, ProductId, BatchId
Edition 2 Optimized has the following priority on the PK: UserId, ProductId, BatchId, LocationId.

How come Edition 2 have to scan over a lot more indexes than Edition 2 Optimized? As I can see it this shouldn't have happened since LocationId = 1 all the time. Or am I missing something?

[edit] <img> tag didn't work so I have to just paste the url

What happens if you put locationId = 1 in your query. Its all about selectivity.

What other columns are on the table. I would also look at what happens if you include userid in the group by.

Can you capture the two query plans.

|||

Yes, thank you

It did help a lot to include LocationId = 1 in the query, when I did this in Edition 2 it became just similar to Edition 2 Optimized. I couldn't see any performance increase by including UserId in the GROUP BY.

The other fields/columns are just a TransactionDate (date for the insert) and TransactionId (identity to make it unqiue for stopping a duplicate insert).

The two Execution Plans can be found at http://www.lostfields.com/sqlindexing_plan.gif

I'm not sure why Edition 2 Optimized has a sort method there though, as Edition 2 doesn't, but have a filter (since LocationId isn't in where clause I guess).

Different Execution Plans -> Same Query, Same Database, Different SQL Server Install

Background: Same database, restored from development to production.

Indexes up to date. Service Pack 3 on Production. Originally RTM on Development, upgraded to Sp4, Execution Plan remained the same.

The query itself is not my concern, but that there is such a wide difference in performance between 2 boxes. Is there a setting on the Production that might be slowing it down? My system seems to have selected a worktable, is there a way I can turn that off to mimic the server?

I want to optimize the queries to perform best on the Production server, which is hard to do if my server's chosing different execution plans.

Is there anyway to determine why the Production machine would choose one plan vs. the development machine?
Development:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Index Spool(SEEK:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
| |--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

Production:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]), WHERE:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

The following is the output of the statistics on my Local Development
Server:
Table 'CONSUMER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3187 ms, elapsed time = 1877 ms.
This is the output of the statistics on the Production environment:
Table 'CONSUMER'. Scan count 88857, logical reads 274907, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 41708, logical reads 48294390, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 690218 ms, elapsed time = 225583 ms.

Have you checked that the statistics are equal for the indexes on both servers?

Look for DBCC SHOW_STATISTICS in the BOL.
|||

Try and rebuild the Indexes on Production Database

DBCC DBREINDEX

|||

The databases were identical, both restored from the same backup resources on the same day.

Thank you for your responses, and I will attempt both, but I don't believe that the statistics or indexes would be different if drawn from the same resource?

|||This is not due to statistics or indexes. It should be identical when you restore from a backup of a database. There are other factors that can affect the query plan - number of processors, memory, maxdop settings etc. Additionally you should try to keep the service pack level the same between the machines. The main reason is that you could get different plans due to changes in service pack and some could be bugs also. So it is best to compare servers that are on the same service pack too. If you are seeing worse performance than SP3 for the same query/machine configuration then it is most probably a bug. I would encourage you to create a bug using the MSDN Product Feedback Center with the repro steps. Also, please use SET STATISTICS PROFILE to check the actual query plan/execution times.|||

It should indeed be identical when you restore but a colleague here has experienced some strange behavior after a restore several times. He claims that after an update of the statistics everything functioned as expected. Now I can't say I fully support this statement as I have no personal experience with this.

We tend to keep our service packs identical to production, I think this should be a rule for every development environment.

You should update your weblog Uma, you still have to answer my NOLOCK question :-)

|||

Thanks for your reply,

The production is running SP3a. I tested the query on development environment at both RTM and SP4 (not realizing the production was still only at SP3) and I did get the same plans between RTM and SP4.

The production machine is Windows 2003 server dual processor,Intel Xeon 3.6ghz, with 3.5 GB Memory, it is part of a cluster server.
The development machine is Windows XP dual processor, Intel Pentium 4 2.6ghz, with 1 GB Memory.

I will compare the profiles in the am.