Hi
we have a windows 2003 server with 4 xeon 3Gh and 8 gb of memory.
Now, when run a query or when click for properties is slowwwww very slow.
No error in the event view
Any idea
TIA
Abel
:angel:
I was hoping I wouldn't be another poster with performance issues after migrating to SQl 2005 from SQL 2000 but here I am.
I am in the process of testing out our databases on Sql Server 2005 for migration from SQL Server 2000 and there are certain portions of code that have been affected negatively. I have read thru many of the posts here and have tried out most of the recommendations. I will start out with things I've done and then provide the actual SQL.
1) I have rebuilt all indexes ( using the DBCC REINDEX using the table option).
2) Updated the db engine to latest hot fix (build 3239) that addresses speed related fixes.
3) I also ran sp_createstats using the 'fullscan' option to create stats on all columns of all tables (minus indexed columns)
4) Since nothing seemed to work, I even ran UPDATE STATICS with FULL SCAN on all tables even though I did not need it as the REBUILD woudl have created stats. But I was willing to try anything.
I have confirmed that the execution plans are different even though the data on both sql 2000 and sql 2005 are identical (i put a copy on 2005). The plans themselves are huge as the queries are huge. Here is the query.
SELECT InterimView.* ,TestView.*
FROM View_LabDataExport_TestFormData_55 TestView
RIGHT OUTER JOIN ( SELECT ReqView.*, CDView.*
FROM View_LabDataExport_FormData_55 ReqView
LEFT OUTER JOIN View_LabDataExport_FormData_CD_55 CDView
ON ( CDView.DB_SubjectID_CD = ReqView.DB_SUbjectID )
) InterimView
ON ( InterimView.DB_FormID = TestView.DB_FormID_T AND
InterimView.DB_LabSampleID = TestView.DB_LabSampleID_T )
The above query takes abotu 8 secs to run on 2000 and about 1 minute to run on 2005. This is for a small dataset and on larger datasets this is only going to more pronounced ( as confirmed by other teams that have already migrated in my company). Another point worth mentioning might be if I remove the TestView.* from the select list, it works in 5 to 6 seconds. Is there an issue with Sql 2005 and a large number of columns or anything of that sort? On 2000, the time remains the same , about 8 seconds if I remove this from the select list.
Here is the statistics ion on 2005
(21234 row(s) affected)
Table 'Worktable'. Scan count 75490, logical reads 3676867, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTestToReportPanel'. Scan count 476, logical reads 1524, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReportPanel'. Scan count 0, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DiscreteValue'. Scan count 1, logical reads 176106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSample'. Scan count 1360, logical reads 18567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Form'. Scan count 2302, logical reads 8225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabTest'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDef'. Scan count 1, logical reads 10530, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabArea'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Lab'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Location'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Study'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Item'. Scan count 1335, logical reads 32940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ObjectState'. Scan count 1, logical reads 10972, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Object'. Scan count 0, logical reads 20674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Subject'. Scan count 0, logical reads 3293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDef'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedLabSampleLabel'. Scan count 0, logical reads 13144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PrintedForm'. Scan count 0, logical reads 4219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudySite'. Scan count 0, logical reads 2756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StudyEventDef'. Scan count 0, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Here is the statistics ion on 2000
Table 'LabTestToReportPanel'. Scan count 2123, logical reads 4820, physical reads 44, read-ahead reads 0.
Table 'LabReportPanel'. Scan count 130, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'DiscreteValue'. Scan count 103914, logical reads 208214, physical reads 0, read-ahead reads 0.
Table 'Location'. Scan count 19031, logical reads 38062, physical reads 2, read-ahead reads 0.
Table 'Lab'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabArea'. Scan count 19031, logical reads 38062, physical reads 0, read-ahead reads 0.
Table 'LabSampleDef'. Scan count 24670, logical reads 49340, physical reads 0, read-ahead reads 0.
Table 'LabTest'. Scan count 19406, logical reads 39575, physical reads 0, read-ahead reads 0.
Table 'LabReleasedSampleTest'. Scan count 4289, logical reads 73865, physical reads 1014, read-ahead reads 24.
Table 'Study'. Scan count 4291, logical reads 8582, physical reads 0, read-ahead reads 0.
Table 'LabSample'. Scan count 5647, logical reads 31382, physical reads 308, read-ahead reads 4.
Table 'Form'. Scan count 4291, logical reads 9272, physical reads 2, read-ahead reads 10.
Table 'PrintedLabSampleLabel'. Scan count 4289, logical reads 17097, physical reads 114, read-ahead reads 308.
Table 'ObjectState'. Scan count 6860, logical reads 13760, physical reads 1, read-ahead reads 0.
Table 'Object'. Scan count 6860, logical reads 23559, physical reads 90, read-ahead reads 701.
Table 'PrintedForm'. Scan count 1375, logical reads 4505, physical reads 40, read-ahead reads 16.
Table 'StudySite'. Scan count 1378, logical reads 2756, physical reads 4, read-ahead reads 0.
Table 'Subject'. Scan count 1599, logical reads 3332, physical reads 2, read-ahead reads 0.
Table 'StudyEvent'. Scan count 18, logical reads 52, physical reads 0, read-ahead reads 0.
Table 'StudyEventDef'. Scan count 18, logical reads 54, physical reads 0, read-ahead reads 2.
Table 'FormDefToStudyEventDef'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 23.
Table 'FormDef'. Scan count 2, logical reads 78, physical reads 1, read-ahead reads 4.
Table 'LabSampleDefToFormDef'. Scan count 1, logical reads 308, physical reads 1, read-ahead reads 306.
Table 'Item'. Scan count 1335, logical reads 36510, physical reads 140, read-ahead reads 1047.
(21234 row(s) affected)
(147 row(s) affected)
One difference between the two is the work table that 2005 creates versus 2000. I can attach the plans but they are huge. I will attach it if you ask.
What I was looking for was suggestions on what I could do short of rewriting code or any suggestions in general.
Thanks
Hi all,I have a Windows 2003 server, which is also a terminal server forapplication, with sql 2000 installed. My company has developed anapplication that uses SQL 2000 as its database. The application is aclient/server one. In each client computer there's a link to theapplication on the server. There is no problem with Windows 98,Windows 2000 pro, Windows xp pro clients, but the windows 95 onescannot log in to the database. The log of the application shows thefollowing error:connection error -2147467259. Cannot open database requested in login'database name'. Login fails.Till a week ago the application was running on a Windows 2000 serverwith SQL 2000 install and the W95 clients had no problem connecting tothe database, so my guess is the error has something to do withWindows 2003 server, but what'causing the error?I tried to install a newer version of MDAC (MDAC 2.5, the last versionof MDAC you can install on W95)but with no success. By the way W95clients have no problem accessing shared folder on the Windows 2003server.Any idea?ThanksMarino
View 1 Replies View RelatedHello, we currently have our database (MSSQL 2005) on our web server however to do increased traffic and business we are now moving our database to its own server. I was wondering if anyone here knew of some good ways to setup/tune Windows Server 2003 and SQL 2005 for best performance. MSSQL will be the only application running on the server and want to make sure it is as fast as possible!
Thanks in advance!!!
I am having major performance issues with Microsoft SQL 2005 x64 Standard Editions performance on Windows Server 2003 x64. The PC has two quad core cpu's with 8gb of ram and running a 500gb mirrored SCSI (Raid 1) drive system. The database running on the server is about 11gb. I've run a defrag several times which helps a little but I was hoping I could do something else to increase the performance.
I have also found that the bottle neck in the SSIS package is the backup and restore process of an 11gb database which takes about 1 hour (backup takes 1 hour and restore takes 1 hour) when it should take about 11 minutes. Is there anything I can do to make these processes run faster or to find out why they are taking so long? Any ideas would be a great help.
Thanks,
100011
Greetings,
We are having major performance issues with Microsoft SQL 2005 64bit Standard Editions performance on Windows Server 2003. We have an SSIS package running very slowly (and other sql tasks) on our two quad core cpu system with 8gb of ram and running a 500gb mirrored SCSI (Raid 1) drive system. The database running on the server is about 11gb.
Does anybody have any suggestion we could try to increase the performance of the server? I've run a defrag several times which helps a little but I was hoping I could do something else to increase the performance.
Thank you.
When windows 2003 server is not log on to the domain, SQL worksfine.(workgroup) Everyone can connect to SQL. When everyone is on thedomain, nothing can connect remotely to SQL.
View 1 Replies View RelatedI'm working on deploying an ASP.NET 2.0 app onto a Windows 2003 server and I'm having trouble with SQL 2000 authentication. When I run my app from my development box running IIS 5.1 I have no problems. In order to get it to work on my this machine, I had set the account for anonymous access to run under an ASPNETDOMAIN account and configured my SQL database to allow this account.On the Windows 2003 box with IIS 6.0, I've done the same thing with anonymous access for the web app, and also created an application pool that the app runs under and have set the application pool to run under the same account. It appears the app is looking for a local SQL 2005 instance (which doesn't exist). Why would it do this and what do I need to change for this to work?Error:An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)Stack Trace:[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735043 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
Hello y'all,
I have to do a new install of SERVER 2000 using WINDOWS 2003 . Has anyone done this. If so, what are the steps. Should I expect any problems ? Any help appreciated.
Thanx
I have XP workstations and windows 2003 SBS. I setup an ODBC for anapplication (tried both User and System DSN), but after a few usage, I getthe following error:Connection failed:SQLState: 'HY000'SQL Server Error: 0[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context.Any ideas? Is this a bug?--Thank youPlease post only
View 3 Replies View RelatedI installed MS SQL 2000 on a Windows 2003 server cluster (no servicepacks). Both nodes are online and both domain controllers are online.I tried to install SP3 and got the following error:--------------Logon Account could not be validated.No authority could be contacted for authentication.---------------I installed SQL SP 2 and did not get the error but I do get it everytime I try to install SP3.Does anybody have an idea that might help resolve the problem.
View 2 Replies View RelatedHello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedCan anyone confirm that SQL 2000 Standard Edition is still limited to 2GB of memory utilization on Windows 2003 Server?
In addition, anyone have suggestions for deploying SQL 2000 on Windows 2003?
TIA
Hi !when installing sqlserver 2000 on a Windows 2003 server it explicitlytells "server not compatible with Windows 2003" during install, but itcan carry on.After we applied serfice pack 3a then the db server seems to runnormally, but does it risk to behave randomly afterwards ???Is sqlserver 2000 standard edition compatible with Windows 2003 server?Or does it exist a specific sqlserver edition for Win2003 ?thanks !Patrice
View 2 Replies View RelatedDoes SQL Server 2000 CE 2.0 work on Windows Mobile 2003?
Thank you,
Ken Carlino
ServiceMaster
Hello there,
We got SQL Server 2000 Standard Edition SP3 running on Windows 2003 server.
Now, we would like to apply windows 2003 SP1 to the Operating System.
We are concerned that whether windows 2003 SP1 is compatible with SQL server 2000 (SP3)?
I was looking at the application comaptibility chart for Windows 2003 SP1.Among them SQL Server 2000 Enterprise Edition Service Pack 3a was listed but not the Standard Edition.
My Question - Is windows 2003 server SP1 is compatible with SQL Server 2000 (SP3)?
Thanks
Can SQL 2000 32 bit run on a Windows 2003 64-bit Intel OS installation? What about the SQL 2000 64 bit version? Is the Enterprise Edition required for this?
View 1 Replies View RelatedI have been running Windows 2003 64bit and SQL2000 SP4 for over 2 months with out any issues.
Sadly we had a server crash "NTldr missing or corrupted"
now when the one engineer looked at it he suggested to reload the server with Windows 2003 32 bit version as there were known compatibility problems between 2003 64bit and SQL 2000 32bit but that you can install SQL 2000 32bit onto a Windows 2003 64 bit server.
Now i dont really believe this statement nor can i find any documentation supporting this. I believe this server might have crashed due to a new patch that was released the last 2 or 3 week either on Windows or SQL. Does anyone know where i can verify what patches was released?
Maybe someone else can share their thoughts with me.
After differential restore I start Remedy service. It starts in few seconds.
After full restore the same service takes 15 minutes to start. Bothe the things are done through SQL service agent. Even manual restaring the service also takes 15 minutes after full restore. WHy is it happening this way?
I am looking for some recommendations for memory sizing and options for a SQL 2000 Cluster. This is a two node cluster built on Windows 2003 ENT SP1 (x86). Both the nodes have the following hardware:
- 4 x Dual Core AMD Processors
- 16 GB Memory
- EMC Shared Disk
We are running six SQL 2000 instances and don't expect each of these instances to use more than 1.7 GB of memory. All these instances are going to support BizTalk 2004 Databases. I already have /PAE enabled on the nodes. I am looking for the following answers:
- Do I need to enable AWE on all the instances even if the instances ? Currently, we don't have that enabled and we have seen some issues regarding excessive paging even when there is physical memory available. The DBAs think that we don't need to enable AWE. I am bit confused on this one.
- We normally run 3 instances on each node and would like size the cluster in such a way that it can take six instances in case of a node failure
Any input will be highly appreciated.
I have a Windows 2003 Enterprise x64 edition cluster setup and functioning normally. Now, I am trying to get SQL Server 2000 installed as a failover cluster but am having some difficulty. When installing, I get the message '[sqsrvres] ODBC sqldriverconnect failed' in the event log. The message happens when the installer is trying to bring the SQL Server Service resource online.
I am able to ping the name of the instance successfully. I am able to manually start the 'MSSQL$InstanceName' service. I have turned off the firewall on both machines, but this did not help. I have the DTC Service setup as a resource in the same cluster group.
I also read http://support.microsoft.com/default.aspx?scid=kb;en-us;815431 which seemed promising, but did not reolve the problem.
Any help would be much appreciated.
Andy
I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!
I'm having issues getting this to work under Windows Server 2003 X64.
I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....
My question is: Were do I get the X64 version of these tools?
sqlce30setupen.msi
sql2Ken@P4.msi
The SQL environment is X86 as follows: SQL2000 SP4
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Any help would be much appreciated!
Will it be possible to do an in-place upgrade from SQL 2000 Server Enterprise SP4 32 bit running on top of 64 bit Windows 2003 Enterprise , clustered, to SQL 2005 Enterprise 64 bit?
The 32 bit SQL 2000 to 64 bit SQL 2005 in place upgrade seems questionable to me...
Anybody tried anything like this?
Currently a server running 2003 small business operating system has MSDE 2000 and Microsoft##SSEE.
A memory problem exists with SQL consuming over 50% of RAM. Total RAM is 4GB.
There is a SQL 2000 patch for this type of problem. However, it calls for SQL to be upgraded to SP4.
Problem:
Can this MSDE 2000 be upgraded with SP4 and have memory patch applied with Microsoft##SSEE also on this box?
Please let me know. Thank you.
we have run access 2003 application using SQL 2005 server
and the data transfer rate is extreamly slow.
we are using ODBC connection
and the access apllication works very fast with SQL 2000.
we have the tested the comptabilty of the application to SQL 2005 with Microsoft wizard and it had no problems.
even when i run simple application on the sql 2005 server, it runs very slowly.
i am using the MDAC clients ODBC component and the SQL Native Clients one.
which eliminates the possibilty of network problem.
what could be the problem???
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedHi,
I have just upgrade my sql 6.5 to 7.0 version. This sql box (compaq proliant 5500) has 1 gig ram and 4 pentium pro processors and smart array controller. There are about 200 users hit on that server constantly. I configured this server according compaq white paper and Microsoft recommendation; however, I am still suffering huge performance hit. Then I setup performance monitor to see what is happening. What I see is all of 4 processors are at 70% processor time constantly. What I heard is sql 7.0 runs much better on pentium III processors. Is that true? Or you have any recommendations??
Help!!
Thanks!!
Jim Zhong
Hello Gurus,SQL Server 2000Windows 2003 Server, Standard Edition.Firstly I'm not a SQL server DBA but have a little experience withOracle 9i and Oracle Rdb.An application that I've inherited has started performing very veryslowly over the last few days, as far as I know there have been nomodifications or changes in the volume of data the db is holding. Thedatabase is in simple logging mode and it's updated twice per day fromand Oracle Rdb database.The SQL database is all in the primary filegroup and is sat on fourdisks (RAID mirrored) which form one logical disk of 128gb. Itconsists of approx 120 tables, each with a primary key and a number ofunique indicies per table.Apart from a twice a day update in the small hours the only access tothis database is read only. Users are connecting via the web andusing predefined Crystal reports to retrieve data.Questions then:Should this db have more than one filegroup and should I put theindicies in a different group? Is this relevant when the underlyingstorage is RAID?Should it be using mirrored RAID or should it be striped or should Isteer clear of RAID for a database?The indicies have not been rebuilt for some time (probably one year).Is this something I should be doing once per day/week as I do have thedowntime window?Should I be doing a database/table reorg at fairly regular intervals?I'm going to generate a workload file from SQL profiler and see what,in conjunction with the Index tuning wizard it suggests.Appreciate that poor performance & tuning is a huge subject and not anexact science but any tips or comments would be gratefully received.RegardsDave.
View 2 Replies View RelatedHi
I have the following structure:
At server, I have SQL Server 2005 with a database running in compability mode 8.0 (SQL 2000). At desktops (cliente side) I have SQL Server 2005 Express. The base on the desktops access the base on the server to a copy of data through Linked Server. Both bases are in compability mode 8.0.
This is my problem:
The copy of data using a ad-hoc query INSERT INTO LOCAL-DATABASE ... SELECT .... FROM REMOTE-DATABASE (WAN) takes much more time than if I use MSDE in the desktops.
I would like to know what problem can be cause this delay.
Thanks
Is there a way to improve this query?
The execution plan states that it's ,aking use of the Indexes as 'Clustered Index Seeks'
but the query takes 30 minutes to complete.
The Index statistics are also up to date!
If I use just an INNER JOIN the query completes in 2 seconds!
How can I make the LEFT OUTER JOIN more optimal?
SELECT A.MeterSerial AS 'PeaceMeter', B.MeterFrom, B.MeterTo,
CASE WHEN A.MeterSerial = B.MeterFrom
OR A.MeterSerial = B.MeterTo
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
THEN 'Y' ELSE 'N' END AS 'ComplexMeter', B.ComplexMeterType
FROM Peace.MeterData_DR1 A LEFT OUTER JOIN
(SELECT MeterFrom, MeterTo, ComplexMeterType
FROM Peace.ComplexMeters2) B ON A.MeterSerial = B.MeterFrom
OR A.MeterSerial BETWEEN B.MeterFrom AND B.MeterTo
WHERE A.MeterSerial != ''
Hi,I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET. I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...Thanks in advance Tom
View 1 Replies View RelatedHi,
I created a cube in my development box, tested, performance is great, now if I try to deploy it into the production server from VS, the screen freezes without any error msg... after some time playing around with security I gave up and created a backup of the cube, and sent it over to the IT department( I have no direct control over the production server so they restored the cube ). Now with the cube in production, it was a matter of just processing it... well that didn't go well either... it would take forever from my development box to see results... then I tried to browse any dimension using the data already on the backup... a simple 4 values dimension would take 2-4 minutes to load on screen...
I can't understand why browsing the cube its so slow in the production server, IT admin even reported that when trying to browse locally it would be slow too... The server has 16gbs of RAM and its a dual processor, he didnt notice any lack of CPU or memory while browsing the cube...
Have you experienced this or can you help me troubleshoot whats going on?
We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?
Here is a snippet of code I used to test the speed against 2000:
declare @counter int
set @counter = 1
while @counter < 200
BEGIN
update UPR40500 set actindx = 96 where actindx = 96
set @counter = @counter+1
END
This takes 15 seconds when it used to take virtually no time at all.