only matter of a different OS there could be differences in IO and disk subsystem, ram, database fragmentation and file system fragmentation do you have something scheduled on the XP machine that loads the pc? are the machines involved do have the same load? it's always tricky to compare different systems with different platforms Andrea, the problem is not the difference in performance neither best result, but the fact that in W2000 I have the same response time for the same application function repeated n times (connected to the same database resided in XP) and "progressive slower" when application runs in XP (same program, same function, same database). The strange is that after a reboot the problem persist and the minimum response time return
Hi, I noticed messages in my sql server log that says: This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected. The question: Is this message is generated each time such situation occure? I get only less than 10 such messages a day - can it be an explanation of slow performance of my system? Vycka P.S. SQL Server version - 2000, personal edition.hi, yes http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp only in the timeframe(s) where the limit has been exceeded, otherway the bottleneck is somewhere else when the number of concurrent workloads reduces within the limit, all activities will regain their original speed..
was named sqlservr.exe! It almost looked like the installation of my MSDE instance was like a totally independent sql server installation! Like if it had nothing to do with the other MSDE instance I had running. I doubt this is the case since I am pretty sure there must be a gazzilion other files installed on the computer that are sheared by all the database that use the MSDE engine. So my question is, 1. Why did the instance of MSDE crated all of this extra files (such as sqlservr.exe)? Instead of simply created the ldf and mdf files like a regular SQL server database? Aren't the MSDE and SQL server supposed to be the same thing? 2. Also is there a special reason for the "$" character"?Hi Rene, Welcome to use MSDN Managed Newsgroup! be The SQL Server 2000
Question regarding the MSDE SP3a install process What happens if I attempt to install MSDE on a server that already has MSDE installed and the SA password has been changed to an unknown password? Can I still install an instance for my database without knowing the SA password? If not, how can I get around this issue? I can see my app/database being installed at a customers site that already has a version of MSDE for a different application they have already installed and are using. Jim K.You should be able to install using Windows authentication, assuming you know the password for an administrator on the box. With that, you can also reset the sa password for an existing instance using sp_password.
trying to get around the current Jet hang problem people are seeing under Windows 2003 and the jet oledb provider, we have ended up having to convert many existing MS Access web site projects to MSDE. The clients don't have the budgets to license full sql server, and don't need the full performance you see there versus the "limiters" put into MSDE, so we have gone this route until the jet hang problem is resolved. My question is: what are the memory and/or performance issues when MSDE is installed on a web server providing db services to multiple sites? I mean, is it just one instance of MSDE running and providing all db services, or is it doing anything less efficient other than the limiters it has in place? How many instances of sqlservr.exe processes should I see -
DBCC execution completed. If DBCC printed error messages, contact your
This indicates I've not went over any limits.
Can anyone give a definitive answer on this one?
TostaoThanks for the prompt response Andrea. I should add that the MVP who gave the contradicitory info was from microsoft.public.access and not this NG. One final question. I'm a Sys Admin not a DBA, so rather than use the DBCC CONCURRENCYVIOLATION function, is there a Performance Monitor counter that I can use to see if I'm nearing the 8 concurrent operations on my MSDE instance?hi, not that I'm aware of.. and I think becouse DBCC CONCURRENCYVIOLATION is not evaluated at all on full blown SQL Server editions and oly returnsThere will be a message in the SQL Server
Hello, We have an application that collects data into and access db. Then the user connects to the internet and the access db syncs with our sql using a vb.net application. We have some issues at times with this vb.net application. I am wondering if we use msde on the client machine and they connect to the internet if there is a way to auto sync the data through sql thus removing the vb.net application. Is this possible could someone give me the pros and cons? We are also hoping to add photos into msde if possible. Thanks in advance. JakeSure, MSDE and SQL Server can replicate to each other (if that's what you mean). It works fine according to most reports. No, I would not store BLOBs (pictures) in the database--even SQL Server Standard that has a lot more capacity than
hi, please do not repeat your question that many times.. it does not help :D actually you have to add another insert statement, INSERT INTO dbo.[Kit Table] (KitItemID, ComponentItemID , Quantity ) SELECT i.Id /* this is the ID of dbo.Item inserted row */ , i.Id /* do not understand what you mean for this column.. your dbo.Item table seems not to have such an attribute in the predicate do you perhaps have an Identity column somewhere? */ , 0.25 FROM inserted AS i WHERE i.DepartmentID = 7; --
speak about 25 concurents users (means concurents operations?) http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp (see the "Can I use MSDE as a database for Web applications?" question) The SQL Server 2000 Workload Governor speak about 8 concurents operations http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp Who's right? What's the exact number limitation for it is neither. As I tried to explain, MSDE doesn't have a limit on concurrent users at all (not 5, not 8, not 10, not 25, not 5000). There is a governor that throttles performance at or around 5 simultaneous workloads, which is *NOT* the same as a connection. The 25 was a *suggestion* on one of the product pages, for how many concurrent users a typical system running MSDE could support.
We have a VB6 app that uses an MSDE SP3 database. We use a process with transactions to add data to the database. As several similar packets of data are added we've noticed a degradation in the speed of loading the information. We've also noticed that if at the end of the insert of several packets of data we get a 30 second upload time per packet, then shut down MSDE, start it up again and then insert in an almost identical packet of data, it will run twice to 3 times faster. We tried turning autoshrink and auclose off but that didn't seem to help. Terryhi Terry, it can depend on the transaction if you load a big transaction, you will see a degradation as the transaction size increses you can perhaps separate the load into separated transactions..Thanks for
MSDE with no degradation. OLTP leans towards the simple side (volume-wise you might have 70-80% of your transactions in the simple category) so lets assume 75 transactions per minute. The real question for MSDE thus is, how many users does it take to generate 75 transactions in a minute. If your users submit one transaction per minute then you could support 75 users. If One part of the requirements analysis dictated that hard concurrent user limits cause major customer dissatisfaction. So MSDE was not given a hard concurrent user limit nor was the governor set to make performance fall off a cliff after 10 users. The result is that for many applications MSDE supports far larger numbers of users than the technical specs might indicate. Hal Berenson, President PredictableIT,
I need to have someone clarify a couple things for me. 1. If a system running MSDE is rebooted in the middle of a transaction, I am assuming at some point when the system is restarted, the transaction log file is rolled back into the main database file. However, if a database is detached before anyone connects to it, will the uncommitted transaction log data be rolled into the main database file before the detach is performed? 2. When exactly does the uncommitted transaction log data get rolled back into the database after a reboot in the middle of a transaction?hi Ken, when a system stops (crashes or is unproperly shut down), all the pending transactions are aborted .. when the SQL Service restarts, it will stars and recover all databases that's to say committed
those counters remotely. the rest had varius behavior i tried lodctr, unlodctr, exctrlst, diskperf -Y and re-register sqlctr80.dll with regsvr32 - with no maijor success. even though i got an error message for the command "regsvr32 sqlctr80.dll" about an entry point that it couldnt find, i don't think that's the problem. any more assistance will be appreciated. thanks, em.Can you please clarify if you can access the performance counters on the local machine and the problem is with remote access? How is that one machine that works fine different than the others? Are there any related messages in Event Log or the log files? Plamen Ratchev http://www.SQLStudio.com
So, I changed things so that each time a meter speaks, I check the database to see if it has been a minute or more since the last database update for that meter, and if it has I write a record out. Now this works fine, but it uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig Pentium 3 system is hijacked. So, my question is: is my SQL (which calls the DATEDIFF() and MAX() functions) the likely culprit for this inefficiency? My colleague believes that it is, since (he says) calling MAX() forces a search through all the records contained in the table (potentially a boatload). Here's the query: SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from " + MeterName Thanks in advance for any advice on improving ANY of this
Hello, Does someone know when Microsoft will releade a new version of MSDE 2000 (sp4??) that will install on a computer without server service (NT) of file sharing enabled (98) and that will (most important) not destroy MDac 2.8 by replacing it with MDac 2.7. It is very annoying. If the administrator install MSDE then he must not forget to install again MDac 2.8 after MSDE Marc Allard Allcomphi Marc, I think SQL Server sp4 havs to wait a little in order to permit SQL Server 2005 working schedules :-( are you sure that MDAC 2.8 is replaced by 2.7? should not be required .. will have a test drive ..hi Marc, did not perform a deep analyses, but Component Checker reports no problem.. did it on Win2k pro RTM thought =;-D added a power user and a restricted user, and, for both,
you can workaround this missing feature some way), DTS runtime has been dropped (but at http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en you can find the Microsoft 2005 Backward Compatibility Components including DTS2000 runtine, which can work with SQLX too).. with the very same (old?) hardware probably you'll not get that much performance benefit.. but "good" news about database limits, FullText and Reporting Services and, of course, all the new CLR features as long as new datatypes and security.. data encryption is embedded in the product of course older applications are not directly SQL Server 2005 "enabled", meaning they do not benefit from these features as
Hi there, How can I monitor the MSDE performance. I read about it on the MSDN site but was not clear what tool to use. Can u help? FPhi FP, depending on what you want to monitor, you can even use the System Monitor, adding your preferred counters, like http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=22222&DisplayTab=Article http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=26380&DisplayTab=Article
and MSDE instance for the first time on a machine, the install states that I must reboot the machine. However if I manually start the instance, then my programs can access that instance without a reboot. 2) If I uninstall the instance done in 1 above, through the Add/Remove programs process, then subsequent installs of the same instance do not prompt for a reboot. So my basic question is: Is a reboot REALLY necessary and if I manually (or programmatically) start the instance? If a reboot is NOT really necessary, is there a command line option that will keep that prompt from being displayed? TIA for all replies Wardell Castleshi Wardell, usually a reboot is required to fullfill and commit all COM pending registrations and services setting and, when prompted, it's usually
from the client's access db to our SQL2k server. We were wanting to remove ACCESS 2003 and use MSDE or SQLExpress and use something to manage the replication between the two(preferably no coding). I have tested MSDE merge with our SQL2K and all works well. The problem is that the SQL client tools are not licensed to work on a client's box that has MSDE. My question is there a tool that will setup/manage the replication between MSDE and SQL2k? What about SQLExpress? We are in the testing phase and are looking for a viable solution with cost in mind(we currently have about 150 remote clients). We will be upgrading to Yukon, replacing our SQL2k box, when it is released. Any suggestions, links to software and documentation would be great! JakeThere will be an Express Manager
using SQL server, sql port is open for everyone, not protected by firewall With sql profiler i see that somebody is tryng to "hack" sa password with bruteforcing.(sa password generator) per. one second this tool trys connect about 5-6 times to sql server. how much is this affecting sql server perfomance? MeelisIt will have a large impact on performance - login is a fairly expensive process. A better question is probably "what will happen to the database when the password is broken?" SQL Server exposed directly to the internet with no protection is a very bad idea.Thnx Roger Whats the best solution for this? Beacuse all clients connecting to this SQL Server have dynamic IP addresses. a) Use Linux based firewall and allow only connect range of
Server 2005 Express. Does exist such comparison somewhere on Internet? 3/ Can be MS SQL Server 2005 Express installed on MS Windows Server 2003 Web Edition? (MS SQL Server 2005 cannot be installed). Thank you, LubomirHi 1. Yes, once it is released. The Beta EULA agreement does not allow for use in production. Look at the scalability limitations on http://www.microsoft.com/sql/express/ 2. As SQL Server 2005 is in Beta, doing benchmarking will not show the true performance of the released product. It is also a violation of EULA. 3. No. It is a Windows 2003 Web Edition licensing limitation where no applications may be installed on the machine unless they are used inside IIS. (http://www.microsoft.com/windowsserver2003/evaluation/overview/web.mspx) Mike
hear many people mentioning Enterprise Manager, but I believe that tool is for the full version of the product. I am able to connect using osql - but all I get is a bunch of numbered lines - I enter in my sql statements but they don't ever appear to run. I tried to install the web-based client, but it is not working because of an error in my webserver configuration. So my question is to you - what tool am I supposed to use to connect to my new MSDE instance? If you could also provide me with some links to docs, I would also appreciate that greatly. Muchas gracias, Rajhi Raj, as you personally experimented, MSED does not come with management client tools but oSql.exe, a command line tool in order to execute your Transact-SQL Statements you have to provide the batch terminator GO
I have a computed field that I want to change the formula. How do I do this in SQL ?You have to drop and recreate the computed column to change the formula: CREATE TABLE a (b INT, c AS b*b) GO ALTER TABLE a DROP COLUMN c GO ALTER TABLE a ADD c AS b*2thanks, i was also thinking of the same way. but dropping and re-creating the field will place the field on the last position. is there anyway to maintain its position to where it was?hi, in a relational database the column position is insignificant and should be the same for client applications as you should not use SELECT * but a well defined select list anyway, you can achieve the desired result creating a temporary table with all the columns in the desired order, migrate data to the new table, drop the old one and renaming the
I am sorry if this question has been posted before, but a search in this newsgroup has not solved the problem at hand: I have installed MSDE on a windows xp pro machine with IIS already running. Later I set up the MS Web data administrator (WDA) However, it is impossble to log in via WDA. As username I have tried "sa", my initials, my windows user name and computername/windowsusername As pw I have tried the strong pw that I used when making
Greetings, We have an application developed in .NET 1.1 (Visual Studio 2003). The application has been using MSDE as the database engine until now. Since MSDE won't be supported on Vista we've tried SQL Server 2005 Express instead. But the performance of 2005 Express is way slower than when using MSDE. What causes this? Is there a way to configure 2005 Express to increase the performance? I'd appreciate any help and suggestions. Nickhi Nick, SQLExpress 2005 is "way larger" as MSDE, in the architectural point of view.. so SQLExpress will run "slower" compared to MSDE on the very same machine.. but, dealing with "upgrades", you can try verifying the database's statistics are up to date as SQL Server/MSDE 2000
Hello, Can anybody tell me about your own experience using IIS with MSDE as repository? Would the concurrent access of the anonymous internet user to the MSDE engine affect its performance if the concurrent access reach more than 5 at a time? Im planning to deploy an Intranet Application for consultant office and Im affraid the MSDE to cause a dregradation in performance if it gets more than 5 concurrent access. Thx in Advance. Mike.hi Mike, you simply have to test, becouse no other project will be the same as your, depending on the base schema, coding conventions, transactions lengths, objects involved, data involved and so on usually it' said that MSDE can afford about 25 concurrent users, but this is not a magic number, only a guessAs Andrea said, there is no fixed
the total memory allocated to SQL (as seen in task manager) is about 6M. this is before any transactions stat with the application. Once the transactions start, the memory usage grows consistantly. After about 12hours of operation, the usage is over 600M (per Task Manager). My question is, is this normal? Can it be controlled? If so, how? If not, what options do I have to manage this? It becomes a problem when the PC running the application must start caching resources in order to maintain SQL performance. Thanks in advance for your help hi rick, please have a look at http://tinyurl.com/5qrjf about memory requirements, and please keep in mind SQL Server works at best when it's running, alone, on a database server, physically separated from an application server, print
Quick question on MSDE maximum connections. If you use MSDE on several and the MSDE on each computers is pointing to one PCs MSDE, does that mean they are always connected regardless if the software you are using is requesting data or not? Meaning if you have 5 PCs pointing to one PC's MSDE, does that mean there are 5 constant concurrent connections? Also, if this is the case, whats the max connections you can have?There is no limitation in connections; but in MSDE the performance will slow down if you have more than 5 concurrent batch (connections) running. After the 5th batch the Query Governer will stop working and will degrade performance. If 5 machines are connected using an application to MSDE, yes there will be 5 connections. HariIt also depends on how the application
runs very fast with about 15000 Addresses in my list. On the 3.06GHz computer it takes about 4 seconds to open only the recordset of the addresses? Why is this computer so slow? does anyone know, how to get this msde faster or are some other thinks to do, like driver update or something else? thanx for your help and sorry for my english ;) Aloishi Alois, performance really are "individual" stuffs :D is the production machine dedicated for SQL Server use only or other services/applications compete for resources on it (is Exchange running on it)? is the production machine hard disk(s) performing well? (or is it a slower one then your?) is it fragmented? is the production database logically fragmented? are production database statistics updated? are the data
I have a question on MSDE2000A's setup.exe. In running the setup.exe /?, it brings up a screen saying that it has an option called: "Display", which can be set to to things like [no | basic | reduce][completion notice] UI: /q[n|b|r][+]. But I can not find documentation for what these values mean. There is also an option called "Apply patch:" which has options of /p SQLRUN | . Does anyoneknow how these
Is anyone aware of any functionally exceptions of the MSDE version of SQL, other than Microsoft's listed ones, I have read in user forums that there are also perfomance knobbling. http://www.microsoft.com/sql/evaluation/overview/default.as p We have the choice of buying a multi-user database with either an Access or MSDE SQL back end, the supplier is currently developing the database to use the SQL backend. We cannot afford to upgrade to the Std version later on.rob, I suspect that if the performance of MSDE becomes too slow due to database throttling that an Access database would also be too slow.
1 cpu (MSDE 2000 allows 2), and 1 GB ram (MSDE 2000 allows 2) use, so my understanding is that things will not change drammatically with the new product Thank you! Unfortunately, I cannot test 30 users in my own office (just not that big) so the only option is to try live at a client site. If this would fail, what would I expect just slower performance? Also, if you are saying that Express and MSDE will have similiar performance results due to different crippling approaches then if I have my app running in an enviornment where it's already a single processor and 1GB RAM and it's running fine there then this might be a mute point - correct?hi Chris, :D you should check for time out problems too in your application code as you already know, when the Governor
very same object, Microsoft decided to design a "standard" for they own uses in Access.. so "Format" is not named that way, but "MS_Format", "Caption" is "MS_Caption" and so on as "MS_InputMask", as this is what Access designers look for Hi Andrea, Yes, that works great. Now for my next question. :) If I use a SQL database tool to view/design my SQL databases will using the 'MS_' format bite me? Should I use both forms of the property name? Or will the 'MS_' form take care of both? Thanks for your help, Charleshi Charles, if you use, say, Enterprise Manager, then you're out of luck for instance, Access names the "
. Is it true? How do I determine that maximum size? Thanks four time [email protected]
Ya, The increase in memory usage may not be abnormal. However, that may indicate that you may have reached the limitation of MSDE. This is documented in Books Online. In MSDE, a concurrent workload governor limits the performance of the database engine. The performance of individual Transact-SQL batches is decreased when more than five batches are executed concurrently. The amount each batch is slowed down depends on how many batches over the five-batch limit are executing concurrently, and the amount of data retrieved by the individual batches. As more batches are executed concurrently, and as more data is retrieved by each batch, the more the governor slows down the individual
after making a change but its the possible corruption and/or stability issues that my not be immediately apparrent (and possibly realized to late!) that worry me. Does this change anything? or is this still a per product issue that requires individual testing? I'm always looking for that nice, simple, yet broad, rule thats easy to remember : )I am referring to both stability, performance and security. Installing AntiVirus on a SQL server increases surface area which has security risks. But what I am really trying to say is you need to test it with your particular anti virus software. For me, I dont want anything actively scanning my SQL server files for a thousand reasons. Antivirus software can certainly create a bottleneck due to the program requiring too much CPU
with msde? TIA Stefanhi Stefan, a similar question has been answered some time ago obviously AV activity can beat heavy loaded machines, but of course I do not recommend not to install AV software if possible just check for the less intrusive one that fit your security requirements.. as regard SQL Server, it does not like other processes "check" it's data files and you will usually define exceptions on them for all data files (*.Mdf and *.Ndf) as long as for t-log files as well (*.Ldf).. this will prevent AV software to scan those files for infections, but those kind of files have never (actually till today) been virus target as regard performance issues, yes, you'll notice performance degradations depending on the intrusive layer of AV