Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Jason — Sun, 17 Aug 2008 22:47:30 GMT

Joe,

Any chance you can rerun the x64 tests with trace flag 834 turned on? I have been meaning to test but I haven't had a chance yet.

http://support.microsoft.com/kb/920093

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Mon, 18 Aug 2008 03:54:27 GMT

I added additional notes to the end. Normally I run with -E -T834, There no meaningful difference compared with default. I did not run with -T834 only. The difference was about 1% either way, depending on the DOP. Lock pages in memory was already set. Apparently the 64-bit OS is aggressive about paging out SQL memory without it. It is possible -T834 will matter more when you really big memory, like 128G+.

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

grumpy old dba — Mon, 18 Aug 2008 07:11:42 GMT

I'm very grateful - solid comparisions between versions of sql server are very time consuming to produce and coming from yourself adds a certain weight when presented to clients. Thanks.

SQL Versions Performance Benchmarks

Grumpy Old DBA — Mon, 18 Aug 2008 07:29:58 GMT

http://sqlblog.com/blogs/joe_chang/archive/2008/08/17/large-query-performance-from-sql-server-2000-to

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Adam Machanic — Mon, 18 Aug 2008 14:57:34 GMT

Joe,

Great post. Can you give a bit more information on the following sentence:

"For SQL Server 2008, the tables use the new Date data type in place of Date Time, and queries are modified to avoid conversion anomalies."

Two questions:

A) How much impact on index size did the modification from date to datetime produce? Even if everything is in memory, reducing the footprint there could create quite a bit of an advantage for 2008. Is this really a fair comparison given that change?

B) How were the queries modified? Just to fix date/datetime-related issues? Or did you make some other changes as well?

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Mon, 18 Aug 2008 21:23:35 GMT

clarifications made above regarding Adams questions

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Guti — Tue, 19 Aug 2008 09:37:48 GMT

Very comprensive post, to get a rought idea on the performance imporvements during SQL Server.

Glad to confirm that x64 versions are considerable faster than its x86 counterparts.

Regards.

Guti (http://guti.bitacoras.com)

Large Query Performance and Query Parallelism

Linchi Shea — Sun, 24 Aug 2008 17:35:08 GMT

Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Leonardo — Wed, 10 Dec 2008 16:38:04 GMT

Very interesting blog.

I found it because i am looking for a response to one project.

The project will manage a database of 20000000 registers,yes, twenty millions registers.

But i am not sure about my recommendation.

I have some questions

Is Sql Server capable to manage a simple query like

Select name,sum(value)

From mytable

from a table with 20000000 registers ???!!!

I use generally that new technology call linq to manage all kind of collections on my projects.

Do you know if would be possible to use link against sql server to retrieve the information with that workaround ?

Thanks in advance,

Leonardo

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Fri, 12 Dec 2008 16:39:49 GMT

of course SQL Server can handle it, but do you really want to return 20M rows frequently? (I am assuming there are 20M names, and some table that lists transactions for each name/register) I do not think that is very smart and will probably swamp the web/app server. LINQ is a way of programming(?), with local storage capabilty(?) and is not meant to be a multi-user data engine(?)

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:38:01 GMT

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Acronyms — Sat, 14 Feb 2009 07:18:49 GMT

Thank you for simple and very visual comparison!

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Soni — Fri, 04 Sep 2009 03:49:56 GMT

Hi,

Currently I am facing a problem. My sqlserver 2005 database has some tables with more than 10M records. Everyday there is transaction of around 50000 records with many concurrent users. We did partitioning with current 3 days data in current partition and rest in other partition but still it doesn't help as due to heavy data the queries run too slow.

Can u throw some light as what would be the solution to solve this problem as we rarely need data beyond 3 days and the other partition also contains data only uptill 3 months. Rest data is deleted as its not required.

Please give some solution.

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Fri, 04 Sep 2009 16:03:29 GMT

go find the SQL Exec Stats tool on my site www.qdpma.com

run it at the end of the day when the plan cache should be fully populated,

this is a matter of query efficiency

10M records is a small database by modern standards

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Roberto Santoro — Thu, 05 Nov 2009 16:05:01 GMT

Great article! I was wondering if you were planning to update the stats now that SP1 for 2008 has come out.

Thanks anyway,

this really made my project much simpler,

Roberto.

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Jame — Mon, 08 Feb 2010 03:22:38 GMT

I have one question. Now my company currently use SQL 2000 server standard edition and enterprise edition on window 2003 server. But now we want to upgrade to window 2008 server 64 bit. Can I run SQL 2000 server 32 bit on window 2008 server 64 bit?

respect all your ideas.

thanks alot

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Tue, 09 Feb 2010 02:16:08 GMT

i recall that SQL 2000 is not supported on W2K8, and may refuse to install

it is possible to hack the registry and make S2K run

but not recommended for production

I suggest putting S2K 32-bit on W2K3 64-bit

but move to S2K5/8 soon

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Jame — Tue, 09 Feb 2010 07:17:49 GMT

Thanks alot Joe.

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Jame — Tue, 16 Mar 2010 09:39:26 GMT

Hi Joe,

I need your idea now. I saw in my SQL server log file.

--Using dynamic lock allocation. [2500] Lock Blocks,[5000] lock owner blocks.

What does mean?

Thanks in advance.

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

kelvin — Wed, 30 Jun 2010 09:39:48 GMT

hi... i'm a newbie in sql 2000 and i wanna know is there a possiblity to get data faster for 2million row....

i really2 need your help because this 2million row will be use frequently....

Thanks in advance... ^^

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

kelvin — Wed, 30 Jun 2010 09:42:21 GMT

oh i forgot to mention that i already try to select all the data and it take around 2 minute but when i put order by... it become 12 minute...

Thanks once again.. ^^

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

jchang — Fri, 02 Jul 2010 00:48:19 GMT

first, you need to figure out if 2min is how long it takes for the SQL server to process the 2M rows, or for the client to receive it

second, the 12min is probably because you have a bad tempdb IO performance,

I suggest you go to www.sql-server-performance.com,

its better set up to for this type of help

also, comment on a more recent blog

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Ekrem — Mon, 04 Jul 2011 08:23:36 GMT

Hi all;

I got a question. I am writing a program which can show informations about processes and i want it to work on lots of different computers. my question is how can i get informations about processes in sql server 2000 and 2005 and 2008?

Do i have to change my query?

My query is for 2008 R2 here:

select top 20

loginame = convert(varchar(128),loginame),

spid,

blocked,

cpu,

physical_io,

memusage, hostname = convert(varchar(128),hostname),

program_name = convert(varchar(128),program_name), hostprocess = convert(varchar(10),hostprocess)

from

master.dbo.sysprocesses (nolock)

order by spid

Thanks a lot...

re: Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

snreddy — Sun, 17 Jul 2011 10:25:02 GMT

good information Nice post the way you have written is appreciable

http://sharepointsolution2010.blogspot.com/2011/07/2000-2005-2008-sql-server-version.html