[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Using named instance (MSDE) much slower than default

Hi there,

I'm using a MSDE database for my application (which is a VC++ app using ADO .NET)
To start with I was using the default instance to test stuff out.  It worked fine.
Now I'm trying to set it up to use a named instance.  Both the named and the default instance are on the computer the app is running from. It works (ie my application is able to get stuff out of the database) but it is way slower than for the default instance, like ten times slower or something.  Does anyone know why?

Thank you.
0
wilsonian
Asked:
wilsonian
  • 7
  • 7
1 Solution
 
SimonLarsenCommented:
Is there a difference in connection style? Have a look in the Network client utility and compare the protocols for each.
0
 
wilsonianAuthor Commented:
Wow, that was quick!  Thank you.  Unfortunately I don't know what the network client utility is.... where do I find it?
0
 
SimonLarsenCommented:
Try start / programs / Sql Server / Client Network utility

filename is cliconfg.exe

Although (maybe quick fingers and slow brain affliction here) maybe it doesn't come as part of MSDE, I know you don't get to install the EM and so on... It is definitely part of a standard sql install, so if you have that disc around you can isntall the client tools and it'll be there.

The reason I assumed you had full sql as default is because I am not sure how you'd go about setting up a named instance with MSDE...
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
wilsonianAuthor Commented:
To use a named instance with MSDE you include INSTANCENAME=<instance name> on the command line when you install, and then put (local)\<instancename> as your data source in the connection string.  I've never used full SQL server (and I only started using MSDE a week or so ago) so I don't know how this compares, but it must be working because it is getting the data out of the database.

I don't appear to have the client network utility on my computer but I do have the disk with full SQL server around here somewhere, I'll dig it out and install the client tools as you suggested.  I'll get back to you when I've done that, thanks for the suggestion.
0
 
wilsonianAuthor Commented:
Ok, I've installed the client network utility and I've opened it up... where should I be looking?
0
 
SimonLarsenCommented:
Generally everwhere, but to start with I'd be looking at the protocols. You just want to try and find a difference between them, if you have the EM you could also compare server settings, especially memory and cpu.
0
 
wilsonianAuthor Commented:
Don't think I got that far... I can see 'General', 'Alias', 'DB-Library options' and 'Network libraries' tabs in the client network utility.  The 'General' one has stuff about protocols, but I don't know how to tell what my instances of MSDE are using.  

The EM is the Enterprise Manager?  I installed that too, what are server settings and how do I compare them? (From the beginning... at the moment I don't even know what the Enterprise Manager does)

Sorry for sounding so dense, as I said I'm new to MSDE, and I don't have much experience with networking stuff in general either.   I have the SQL Server Books Online, perhaps you could tell me what smart things to look up would be and I could try to answer these questions myself?  
0
 
SimonLarsenCommented:
Right, for the Em you want to register your 2 servers.

Open the EM and one will probably be there immediately (maybe both), to add just right click a sql group (probably called local) and do new sql registration. Step through the wizard and ensure you have your 2 sql servers registered.

Then right click each one and choose properties, compare the settings on each tab. Under General is a button for Network, make sure they have the same. Memory and cpu will probably also be important.
0
 
wilsonianAuthor Commented:
Both my named and my default instance are on the same server (the local computer).  I have added it to a group.
Now in that group under Databases I can see the database that belongs to the default instance.  Do your instructions only work if the instances are on different servers or can I modify them for my situation?  Thanks for your patience.
0
 
SimonLarsenCommented:
Effectively the named instance is another server, so if you try to add a new reg and type in the named instance you ought to have 2 servers you can compare settings of. You can alter the setting for each independantly.
0
 
wilsonianAuthor Commented:
Ok, sorry I misunderstood.

...After all that, the settings are identical for both of them.

Hang on, just noticed something... the ldf file for the db in the named instance is about fifteen times the size of the one for the default.  (15MB compared to 1)  This seems like a potential reason for the lack of speed, although I'm not sure what's stored in the ldf file...?

I made both by using the upsizing wizard to convert from access 2000 (on identical mdb files).  I just upsized another copy to the default instance and it's 15MB as well.   Im pretty sure I've done it exactly the same wayeach time but it seems like either a) I'm wrong and I've changed a setting somwhere or b) the default instance started out this big too but something made it get smaller.

Any of this looking like an explanation to you?
0
 
SimonLarsenCommented:
The ldf is the log file. Any change written to the db goes via the log (with some exceptions) so this indicates the volume of changes going through the database

However it truncates quite happily by itself on regular intervals (checkpoints or backups depending on the recovery method used).

Hence if one has a larger this could mean absolutely nothing. However it could be indicative of the volume of data int he databases. If the indexing is not partiularly great (quite possible if upsized from access as indexing is a different kettle of fish on sql) this could be the reason.

Now that you have the 2 db's available you could try running your query in each server via Query Analyser. (Tools / Query Analyser from EM in main window). See if they perform differently there as well.

If they do try turning on the execution plan (Query / Show Execution Plan) and run the query again for each one.

Have a look at the costs of each step looking for differences between the 2 instances. Keep an eye out for high cost operations, especially if they are table scans.

Possibly try running the Index Wizard using the query you have in QA, see what it recommends.

Note if they run the same in QA there is a good chance that someone has changed soemthign in your front end ;)
0
 
illCommented:
ldf file:
it's transaction log. it allows to rollback transactions to recovery points. Perhaps there are still active transactions.
In EM select the database and choose properties from context menu. Switch to option tab and check Recovery model and set it to simple, if not already.
0
 
wilsonianAuthor Commented:
*eye roll* it seems to have fixed itself all of a sudden... guess it couldn't let me look in the QA and find out what was _really_ going on.  Anyway, the named instance version is now going about the same speed as the default.

I'm going to give you the points, SimonLarson, because you were very helpful even if we didn't manage to work out what the problem was.  Thank you!
0
 
SimonLarsenCommented:
heh you gotta love that.

In this case I'd suspect something happened on the server, one possible cause is statistics (read BOL for more) in a nutshell these are indices created by the system when it thinks it needs them, usually these are set to auto create (right click a db choose options to verify).

If you view your db in EM and right click view / taskpad then have a look at the tables tab. If you have a bunch of _wa named things these are statistics. If you have a lot and you don't _know_ why you have them reading BOL about stats and indices is a good idea.

Also maybe look into grabbing a trace file from profiler and looking at expensive operations and seeing if you can index and make 'em happier.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now