October 15, 2009 at 2:42 pm
Hi guys, we have an application on sql express and it runs very slowly. The vendor swears that it will run better on a full standard license and proves it on his laptop.
We I mean slowly I mean 10 seconds to record 1 record on a "supposed" index seek, his words not mine.
Do you guys know of any whitepaper from MS that talks about there being no performance difference between editions (aside from the ram limit and 4 gb db size and cpu limits).
I know those all matter but this app runs a couple 100 queries a day from a single user so those should not matter at all in this case.
TIA.
October 15, 2009 at 2:48 pm
There isn't a query throttle on Express. If the queries aren't pushing the limits on the hardware, there's no reason for there to be a speed difference, at least not that I know of.
I've run plenty of stuff on Express vs Dev (Enterprise) and I've seen functionality differences (Dev will do stuff that Express won't), but I've never yet seen a performance difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 2:55 pm
Ninja's_RGR'us (10/15/2009)
We I mean slowly I mean 10 seconds to record 1 record on a "supposed" index seek, his words not mine.
Can you find/get vendor to find the wait type that the query has while running?
Agree with Gus, there's no throttle on Express. If it's slower than vendor's laptop there's another reason. Seeing if the query's waiting and, if so, on what would help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2009 at 2:56 pm
Depending on the size of your database memory could be a hangup.. But other than the processor, memory, and database size limits Express and Standard a virtually the same. Enterprise does have some additional performance things but that isn't the question.. Also, having nice fresh indexes and stats sure doesn't hurt. Is he using a copy of YOUR database, if not how do we know it is the same structure..
CEWII
October 15, 2009 at 2:59 pm
Of course, if you want to prove this beyond any doubt, either download the trial version, or buy a copy of Dev Edition (worth having anyway), and run the database on one of those. Testing features and performance is a perfectly valid use of either.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 3:00 pm
If time permitted, I would do "in place" version compares. As in - install Express, run various perf tests, then purchase Dev Edition (essentially an Ent. Edition without the pricetag), install that, then run and compare the times.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2009 at 7:22 pm
Thanks guys, I knew I could count on you.
After posting the thread I did a little more digging and apparently hardware limits may be a problem. The DB is about 2.2 GB which is no problem.... unless you have 2 tables with 1.1 GB of data EACH. And that your queries run "seeks" on those. One of those table holds pure pdf file in binary format. The other one seems to contain the text data in a more accessible way. I hardly call that a database but that's what we're working with.
The app basically does ocr on pdfs and saves both version. Now even if the db has 80 tables, apparently only 10 of those are used, and 2 of them contain 99.9999% of the data.
Then in the GUI, the user can do a search on ANY field of any pdf formats in the system (obviously not all indexed, or if they are it's in a element/value combinaison, I'll have to dig it out more when I have time). Everything is supposed to be indexed but I find that hard to believe at this point.
Also more to the point, in the lifetime of this app, the 4 GB limit will definitly become an issue so we might as well plan the migrate asap since we dish out a couple 100 pdfs a day. That next 1.7 gb is only a couple months, maybe even weeks away.
October 15, 2009 at 7:22 pm
GilaMonster (10/15/2009)
Ninja's_RGR'us (10/15/2009)
We I mean slowly I mean 10 seconds to record 1 record on a "supposed" index seek, his words not mine.Can you find/get vendor to find the wait type that the query has while running?
Agree with Gus, there's no throttle on Express. If it's slower than vendor's laptop there's another reason. Seeing if the query's waiting and, if so, on what would help.
How do I do that... I never tuned that way Gail :-P.
October 15, 2009 at 7:27 pm
Elliott W (10/15/2009)
Depending on the size of your database memory could be a hangup.. But other than the processor, memory, and database size limits Express and Standard a virtually the same. Enterprise does have some additional performance things but that isn't the question.. Also, having nice fresh indexes and stats sure doesn't hurt. Is he using a copy of YOUR database, if not how do we know it is the same structure..CEWII
I had a go at rebuilding all the indexes in the DB and that didn't solve it either. There was the auto-close issue which also didn't solve it. And to top it all off the db is set to auto-grow at 1 mb intervals. So after almost 3GB of growth, fragmentation is definitly an issue here. Even more to the point if there's not enough usable ram to hold all the data.
October 15, 2009 at 8:12 pm
was his laptop 64-bit by any chance? Because that would allow him to use twice as much RAM for SQL (since SQL could use 4, and the OS could have 4 of its own....)
You've probably thought of this too, but depending on where Express runs, it could be on a drive that gets a lot of activity, or gets virus checked, etc.... i.e. all of those fun things that slow deskto-type OS'es down.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2009 at 8:14 pm
Just curious but with the size of data you are keeping how will you keep the database under 4GB, which is the database size limit for SQL Server Express?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 16, 2009 at 12:44 am
Ninja's_RGR'us (10/15/2009)
GilaMonster (10/15/2009)
Ninja's_RGR'us (10/15/2009)
We I mean slowly I mean 10 seconds to record 1 record on a "supposed" index seek, his words not mine.Can you find/get vendor to find the wait type that the query has while running?
Agree with Gus, there's no throttle on Express. If it's slower than vendor's laptop there's another reason. Seeing if the query's waiting and, if so, on what would help.
How do I do that... I never tuned that way Gail :-P.
Sorry, assuming that you knew everything. 😀
SELECT * FROM sys.dm_exec_requests where wait_time > 0 and session_id > 50
That should get you all the queries that are waiting for something. Run that while the 10 sec query is running and you should see if it's waiting for something and, if so, what.
Don't suppose you can get at the execution plan to see if those supposed seeks really are seeks?
p.s. how are you keeping? Haven't seen you around for a while. Coming to PASS this year?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 4:24 am
Melton (10/15/2009)
Just curious but with the size of data you are keeping how will you keep the database under 4GB, which is the database size limit for SQL Server Express?
I already answered that in my 4th post in this thread. I think we have at best 2-3 months before we hit that limit... unless the app is redesigend by some miracle.
October 16, 2009 at 4:25 am
Matt Miller (#4) (10/15/2009)
was his laptop 64-bit by any chance? Because that would allow him to use twice as much RAM for SQL (since SQL could use 4, and the OS could have 4 of its own....)You've probably thought of this too, but depending on where Express runs, it could be on a drive that gets a lot of activity, or gets virus checked, etc.... i.e. all of those fun things that slow deskto-type OS'es down.
I'll check it out this morning... I had about 5 minutes to troubleshoot this issue before leaving the office. I know very little about that machine aside that it hosts that application.
October 16, 2009 at 4:32 am
GilaMonster (10/16/2009)
Ninja's_RGR'us (10/15/2009)
GilaMonster (10/15/2009)
Ninja's_RGR'us (10/15/2009)
We I mean slowly I mean 10 seconds to record 1 record on a "supposed" index seek, his words not mine.Can you find/get vendor to find the wait type that the query has while running?
Agree with Gus, there's no throttle on Express. If it's slower than vendor's laptop there's another reason. Seeing if the query's waiting and, if so, on what would help.
How do I do that... I never tuned that way Gail :-P.
Sorry, assuming that you knew everything. 😀
SELECT * FROM sys.dm_exec_requests where wait_time > 0 and session_id > 50
That should get you all the queries that are waiting for something. Run that while the 10 sec query is running and you should see if it's waiting for something and, if so, what.
Don't suppose you can get at the execution plan to see if those supposed seeks really are seeks?
p.s. how are you keeping? Haven't seen you around for a while. Coming to PASS this year?
I was working on that in my 5 minutes window. I came across an app that only executes sp_prepared statements and apparently I cannot find any of the source query about that program. Also the only one I could find does a clustered seek, but it's not on the big tables.
1 - Do you think the exec plans could be saved into trace? I think I remember someone talking about that in PASS 2007.
2 - Yes Gail, I do know a lot, especially when I have time to hit up google :-P.
3 - I have no plans of going to PASS this year. I've been busy with building my own business and getting back in shape. I'm still doing a lot of SQL but right now I'm working with Access / SQL SERVER setup.
4 - Does sys.dm_exec_requests caches the results at all? If so I could just run that query this morning. Timing this query with an actual application that actually takes 5-10 seconds might be tricky with only 1 computer in the room.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply