Very Slow GROUP BY With SQL 2005 On 64bit Multiprocessor Server
Apr 5, 2006
Hi
I have the following configuration:
- Server with 8 AMD Opteron Processors
- 24 GB Memory
- Windows Server 2003 64 bit
- SQL 2005 64 bit
- EMC Storage
The main instance is defined to use 7 Processors and
has about 10 databases.
The problem is:
When I run a "simple" Query (DISTINCT or GROUP BY) on a Table
with something like 1 million rows, SQL 2005 is very very slow !!!
The same query is on a SQL 2000 about 1000times faster ...
Does anybody has some ideas how to configure the system
to make it more useful ... ??
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.
I have a Dual Quad Core Xeon processor server running SQL 2005 Standard. When installed, one processor license was given, even though two OTS boxes are present. When looking at SQL Sever Properties>Processor, it shows 0-7 available CPU's. When running a large table insert and looking into TaskManager, it appears only 1 cpu out of 8 viewed are being hit and hard. The total CPU usage is like 13%. And SQL Server seems slow. Any idea what gives? Why would it show only 1 CPU being hit (maxed out) and the rest doing nothing? Is there a way to tell if SQL Sees all processors (licensing issue?) other than in the SQL Sever Properties>Processor? Thanks.
I just installed the 64bit Vista and than wanted to install the 64bit SQL 2005 Std. Server on it.
Note: I downloaded my license from https://licensing.microsoft.com/eLicense/L1033/ProductDetail.Asp?SKU=228-05338.
Installing from SQLSTDSEL.ISO file results in the warning that for the REPORT- Server the .NET version has to be 32bit.
Stopping the installation at this point and try to install from SW CD SQL Svr Standard Edtn 2005 64Bit X64 English #1 x64 MLF.x11-57664 + SW CD SQL Svr Standard Edtn 2005 64Bit X64 English #2 x64 MLF.x11-57665 does not give the above error but on the other hand installs no server at all since somehow the installation routine is not working at all.
Installing now - after at least analysis server and reports server are 64bit and installed - from the first mentioned ISO results in the installation of the DB- Server.... but as a 32bit installation.
Now I wonder what did not work out - so maybe someone here has an idea if and how this problem is solveable.
We have a 64bit installation of SQL Server (SSIS, SSAS) running and working fine. There is a requirement to install 64bit SSRS on this existing setup. The problem is: While running the SQL Server Setup wizard, the Reporting Services checkbox remains greyed out. In other words, there isnt an option to select SSRS for installation. So the question is: Is it even possible to install 64bit SSRS on an existing 64bit SQL server installation - given that SSRS wasnt installed during the first pass. PS: The SQL Server is SP2 on a Windows Server 2003 configured as an App Server.
This may seem like a completely foolish question, but is there any issue in using the 32bit workstation components (on XP) to connect to the 64bit server-side components? New to SQL2K5 and never had any issues using the 2K workstation/server components together.
I'm having problems installing SP1 on one of our servers.
If I try to install the x64 version of SP1 setup says it can not update my server and I should use x86 version. Trying to install the x86 version results in the message I should use x64 version....
I have implemented a application on SQL Server 2005, this application runs huge queries and some stored procedures. Now that client is shifting on SQL Server 64bit edition, what should i need to worry about. What all changes am i expected to handle.
I would also like to know if there are any other issues to be considered.
Recently, we have migrated our database from 32bit to 64bit. So far the application is working fine.
But I want to confirm that by any tool or automated process.
Is there any way or tool to check that the migrated data is correct and also to check if migrated database objects will work fine in 64 bit environment.
We have an application where part of the database is used for searching products and is very processor intensive due to the number of permutations and calculation that are required. This section of the database is scaled out by using transactional replication so that the €œsearch€? load is removed from central database€™s daily operations.
Each €œsearch€? server can handle around 15,000 search an hour with SQL taking about 3 seconds to return the result in XML (the system has be optimised to squeeze out every last bit of performance). This is in a 32bit environment. One other point, which is probably relevant, is that the Search data only totals 500MB.
With the possibility of upgrading these severs in the near future would we see any performance benefits in going 64bit. I know we won€™t take advantage of increased memory access due to the size of the search data but is there any discernable performance advantage between 32bit v€™s 64 bit processors and SQL Server 2005 64Bit. Or should I stick with SQL Workgroups and 32bit processor and save on the Standard SQL licence?
I'm trying to use application designed for 32bit operating system on 64bit (Longhorn beta 2). This application requires SQL Server CE 2005, I installed it (x32) but the application still says that it isn't.
What can be the problem? I suggest the application cannot retrieve information about SQL Server CE installation. But maybe the reason is I need to install something like SQL Server CE for 64bit operating system? Is it present "in native"?
We recently migrated our 32bit instance of SQL Server 2005 SP2 to a 64bit instance of SQL Server 2005 SP2. The migration was performed by copying all .mdf and .ldf files (including master, tempdb, etc...).
Everything seems to have worked nearly flawlessly except for a few glitches seeming to relate to the master database. I am hoping there is a way to mitigate these issues without reinstalling and performing the migration again.
First, when attempting to view the Activity Monitork, I get the error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)
View or function 'master.sys.dm_exec_sessions' has more column names specified than columns defined. (Microsoft SQL Server, Error: 4502)
Second, the procedure: master.dbo.sp_procedure_params_rowset fails because of the line: option (OPTIMIZE CORRELATED UNION ALL).
Like I said, I know this is probably an issue arising from copying the master database, but if it is at all possible to avoid another migration, I want to.
Mike Thomas Software Developer The Master's College
I have a problem with DTS - ActiveXScripting that it cannot initialize the script engine, I have the backward compatibility stuff installed, even tried a repair but still does not work.
Many thanks for any help you can provide on this problem.
Paul
Error Information below:
Date 17/04/2007 13:07:32 Log Job History (Test ActiveX Script)
Step ID 1 Server GBCONV1A002V03 Job Name Test ActiveX Script Step Name Step 1: Exec TestActiveX script Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message Executed as user: WW004ConSrvSQLAgentA002. ...TSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220487 (800403F9) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220487 (800403F9); Provider Error: 0 (0) Error string: ActiveX Scripting was not able to initialize the script execution engine. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSActiveScriptTask_1' failed. Error source: Microsoft Data Transformation Services (DTS) Package Help file: ... Process Exit Code 1. The step failed.
I have a big problem with creating a linked server to ORACLE database!
- I have installed ORACLE Client 10g on my server (Windows Server 2003 64 bit) - With ORACLE Tools I am able to connect to the ORACLE Server - When I try to connect in SQL Server I get the following error: Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "MISPROD.WORLD".
I have tried I guess all combinations of getting data out of ORACLE ... :-( For example: - SELECT * FROM OPENQUERY([MISPROD.WORLD], 'SELECT * from ORACLE_TABLE') - SELECT * FROM [MISPROD.WORLD]...ORACLE_TABLE I allways get the same error and I dont know what to do ...
Hi All, After I installed sql server 2005 64bit standard edition on Windows Server Enterprise 2008 64bit, I cannot connect to the sql instance using the sql management studio on the same machine! I verified that:
service is running,
in surface area configuration: remote connections to local and remote are enabled, for TCP/IP and named pipes.
ran the command netstat -avn| findstr 49279 to make sure that the server is listening.
firewall is off, but this does not matter since I'm connecting locally to local instance
I'm using domain controller account to login to sql server / also tried the sa account. what else can be wrong?
I just installed SQL Server 2005 64bit on a fresh system with no other applications loaded on running on the new server.
The only other component that was installed with the SS2K5 database services is SSIS. The base installation and the SP2 patch went on perfect.
I went looking for the Management Studio thru the Start menu options and it is NOT under the Microsoft SQL Server 2005 group. The only options under the SS2K5 group are "Configuration Tools" and "Docs and Tutorials".
Here are file names that I downloaded from the MS website and installed.
SW CD SQL Svr Enterprise Edtn 2005 64Bit X64 English #1 x64 MLF.x11-57796 (which is a zipped)
SQLServer2005SP2-KB921896-x64-ENU
There was also another zipped file that I tried to install but it seems to be a duplicate of the zipped file above:
SW CD SQL Svr Enterprise Edtn 2005 64Bit X64 English #2 x64 MLF.x11-57797
We have recently migrated from SQL 2000 (32bit) to SQL 2005 on Win2003 (both are 64bit). And we have Legacy DTS packages on SQL 2005, some packages are pulling records from Oracle 10g (32bit server). When I execute the DTS package I am getting following error.
Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle
Did I need to install/update any MS OLE DB driver for Orace. Some blogs & forums suggesting to install Oracle 10g Client. If so Which version of Oracle client(32bit/64bit) I have to install?.
We have been experiencing issues with SQL Server 2005 SP2 upgrade on cluster servers. After running the setup, it gives failure message for Database services.
Installing SP2 on 64bit cluster server we get error regarding "passive node not patched properly or run the setup from passive node". However, select serverproperty(€˜productlevel€™) returns the output as "SP2". On more digging we found from SQL Server Error log, that the resourcedb of that server is of level 9.0.0.3199
€œThe resource database build version is 9.00.1399. This is an informational message only. No user action is required.€?
However if we do the SQL Server 2005 SP2 upgrade on standalone machines with the same software repository, it upgrades successfully and also resourcedb upgraded to 3042 version. So if we look at the errorlog of a standalone machine it gives following message.
"The resource database build version is 9.00.3042. This is an informational message only. No user action is required."
I again reiterate that, this is the issue we have faced on 64bit SQL Server 2005 SP2 upgrade only on cluster SQL Servers, however on standalone machine it works fine and 32bit SP2 installation works fine.
I installed what I thought was SQL Server 2005 64bit, but I'm not sure. I used my action pack Microsoft SQL Server 2005 CD, and it's on a Windows 2003 64 bit machine. When I click help -> about in the SQL Server Management Console, it says "Microsoft SQL Server 2005". There's no 64 bit in there.
Can someone tell me how I can find out if I've correctly installed the 64 bit version?
Now I'm trying to run PHP web application on x64 2003 Server with Microsoft SQL Server 2005. The version of PHP is 5.2.3. and the web application should be run as 64bit applications. Since there seems no way to use php_mssql.dll on the environment, I'm trying to use SQL Server 2005 Driver for PHP but when PHP loads php_sqlsrv.dll, an error has occurd and there's a following message in an error log...
PHP Warning: PHP Startup: Unable to load dynamic library 'c:phpextphp_sqlsrv.dll' - %1 is not a valid Win32 application
From this message, I thought the distributed libraries for Windows would be for a Win32 environment but there seemed no information that says the libraries run on Win32 environment only.
Has anyone seen a problem where a SQL Server Application works on a server with a single Processor, However when placed on a box with multi-processors it fails. You do not receive any errors but the data set that is returned is not correct. When reviewing this problem on a test machine that only is a single processor we can not duplicate it. The Production Box is Multi Processor 2 pentium pro 200's with SQL Server 7.0 and no Service Packs. My Question is has any one ever seen this problem, and would installing the SP correct it, I plan on installing the SP's but I wonder if this could be the cause of the problem.
We have a sql2005 server, wich have 10 databases mirrored. Yesterday we had the following error:
Date 21-08-2007 14:13:18
Log SQL Server (Current - 22-08-2007 13:00:00)
Source Server
Message
Resource Monitor (0x2d18) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 88080 KB. Approx CPU Used: kernel 234 ms, user 109 ms, Interval: 60000.
The error cause that one of our databases generated an automatic failover.
I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..
please help me..If you need some more information please ask ..
Hi! I have a SQLServer 2005 running om a 64bit cluster. It is used to run a SharePoint 2007 portal. My problem is that the access to the database from the other servers in the farm is very slow.
I made a test program in C# that creates a standard .Net sqlConnection and runs a simple query 100 times. When run on the database server it takes less than a second, but when I run it from the web frontend server it takes 30 seconds. It never fails, it's just slow. The network connection is fast when copying files etc.
I see nothing out of the ordinary in the event log.
Do any of you have an idea what might be the problem or know how I could try to find the problem?
hi~ our system to run the the job with SSIS package is slower than DTS package. The SSIS package action is the same as DTS package. Why? What do I take care??
P.S. When perfoming a simple Query on the databases (with 1.000.000 rows result) with SQL Server Management Studio Express, I see 37 seconds for SQL Server 2000 and 33 Second for SQL Server 2005. That is more the result I expect.
Hello, i want to install the SQL 2005 express Server on a HP-Netserver LH4 with 1 Xeon 500 CPU. I know, that i must have a 600MHz CPU, but i cant change the CPU. Is there one way to start the SQL server? Thanks Michael
Just curious if it's possible, and supported, to upgrade SQL 2005 from32bit to 64bit. This is on top of Windows 2003 64bit. Trying to getthe proper supported config for OM2k7 without blowing everything away.Thanks,JDP
We have a problem with our SQL2000 32 bit to SQL2005 64 bit migration where we use xp_readmail to check mailboxes for new messages containing incoming data. This is no longer supported in SQL2005 64 bit edition. Are there any other methods for checking mail in SQL2005 64 bit edition?
Here is the brief to my problemWe had our database on SQL Server 2000 and Windows 2000.This machine had 2gb of RAM and dual Penitum 3 processors and about 25-30 users were connected all the time. The size of database is around 2 gb. Even on this setup rate of data retrival was good, never had any issues. We moved to SQL Server 2005 and Windows 2003. This machines has 2 Pentium Xeon 3.4 processors and 2 stick of KINGSTON 1024 MB 333 MHZ DDR DIMM ECC CL2.5 DUAL RANK X4 INTEL. The rate of data retrival is awful and its very slow. It using about 1.7 to 1.9 gb of RAM all the time. Page File usage is about 2.07 gb and Virtual Usage is about 1.7gb.I dont quiet understand why is it so slow to get data. We use bespoke software, so nothing has changed there. Hardware specification of our server is far more better then the recommended system requirement for SQL Server 2005.Am i missing something out or i havent set up the SQL Server properly? Any help would really be appreciated.Mits
