I have an SQL Server 2005 instance that slows down over time, she almost grinds to a halt. The data is being exposed via an ASP.Net 2.0 web interface. The web application gets slower and slower over a matter of days. If I restart the SQL Server process she comes back to life and starts serving as it should - nice and snappy.
The web application does not perform much writing to the DB, 90% of the time its just reading. The DB server is worked hard by a console application that produces data each day. This console app runs for about 30 minutes during which there is a lot of reading, processing and writing back to the DB as fast as the hardware will allow. Its this massive workload that is slowing the DB server.
This seems to be related to the amount of memory that SQL Server is using. When looking at Task Manager I can see that sqlservr.exe is using 1,878,904K, this figure continues to rise while the console app runs. I have seen it over 2 GB. When the console app finishes the memory is still allocated and performance is slow. This continues to get worse after a few days of processing.
The machine's specs are:
* Windows Server 2003 R2 Standard
* SQL Server 2005 Standard 9.00.3054.00
* Twin 3.2Ghz Xeons
* 3.5 Gb RAM
I plan to apply "Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2" in a blind hope to solve the problem.
Any suggestions?
Sorry if this is in the wrong place guys, couldn't find a general performance topic. Please move accordingly.
We are running SQL Server 2005 Ent Edition with SP2 on a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction.
I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this.
I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios.
The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again?
If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround)
We have just upgraded to Service Pack 4 on our SQL Server 2000. We have had a DTS job that normally takes about four hours to complete(this dts job has been ok for the last three years). However, after applying SP4, this DTS job now takes over 8 hours to complete.
There are no other processes running on the box and the box is a high end Dell machine with 8 Gig of RAM.
Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.
Perhaps I am doing something really easy completly wrong here.
Why does the local variables in the first code segment slow down the overall execution of the procedure? Dont mind the logic why I have them there are only testing som things out.
If i declare two variables the same way: DECLARE @v INT SET @v = 100
When I use it in a WHERE CLAUSE: ...WHERE [V] BETWEEN @v AND @x) Is there any different then ...WHERE [V] BETWEEN 100 AND 200)
Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?
Thanks in advance /Johan
Slow
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr] @beginDate DATETIME = null, @endDate DATETIME = null, @beginTime INT, @endTime INT, @subscribers VARCHAR(MAX), @exchanges VARCHAR(MAX) = '1:', @beginDateValue int, @endDateValue int AS BEGIN SET NOCOUNT ON;
DECLARE @s INT SET @s = @beginDateValue DECLARE @e INT SET @e = @endDateValue print @s print @e
DECLARE @exch TABLE(Item Varchar(50)) INSERT INTO @exch SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19)) INSERT INTO @subs SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id] ,[Abandon] ,[Bcap] ,[BlId] ,[CallChg] ,[CallIdentifier] ,[ChgInfo] ,[ClId] ,[CustNo] ,[Digits] ,[DigitType] ,[Dnis1] ,[Dnis2] ,[Duration] ,[FgDani] ,[HoundredHourDuration] ,[Name] ,[NameId] ,[Npi] ,[OrigAuxId] ,[OrigId] ,[OrigMin] ,[Origten0] ,[RecNo] ,[RecType] ,[Redir] ,[TerId] ,[TermAuxId] ,[TermMin] ,[Termten0] ,[Timestamp] ,[Ton] ,[Tta] ,[Twt] ,[Level] FROM [dbo].[Cdr] AS C WHERE (C.[DateValue] BETWEEN @s AND @e) AND (C.[TimeValue] BETWEEN @beginTime AND @endTime) AND EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level]) AND (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
END
Fast
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr] @beginDate DATETIME = null, @endDate DATETIME = null, @beginTime INT, @endTime INT, @subscribers VARCHAR(MAX), @exchanges VARCHAR(MAX) = '1:', @beginDateValue int, @endDateValue int AS BEGIN SET NOCOUNT ON;
DECLARE @exch TABLE(Item Varchar(50)) INSERT INTO @exch SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19)) INSERT INTO @subs SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id] ,[Abandon] ,[Bcap] ,[BlId] ,[CallChg] ,[CallIdentifier] ,[ChgInfo] ,[ClId] ,[CustNo] ,[Digits] ,[DigitType] ,[Dnis1] ,[Dnis2] ,[Duration] ,[FgDani] ,[HoundredHourDuration] ,[Name] ,[NameId] ,[Npi] ,[OrigAuxId] ,[OrigId] ,[OrigMin] ,[Origten0] ,[RecNo] ,[RecType] ,[Redir] ,[TerId] ,[TermAuxId] ,[TermMin] ,[Termten0] ,[Timestamp] ,[Ton] ,[Tta] ,[Twt] ,[Level] FROM [dbo].[Cdr] AS C WHERE (C.[DateValue] BETWEEN @beginDateValue AND @endDateValue) AND (C.[TimeValue] BETWEEN @beginTime AND @endTime) AND EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level]) AND (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck. The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting with dbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout). I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though. Any ideas greatfully received, Thanks
Hi All,We're running SQL Server 2000, SP3.I have a stored procedure that consists of a single Select statement.It selects a bunch of columns one of which is a column of data typeTEXT.SP takes 30 sec to run which causes timeouts on the Front End.When I comment out the Text column from the select it only takes 1Sec.Is there anything I can do about it? I know I can't index a Textcolumn. It's also not used in the where clause, so no need forFull-Text Search.But we absolutely have to have it in the Select clause.Thanks for the help in advance.~Narine
Hi all, In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.
Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.
I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be.
I have two tables - gift_cards and history - each related by a field called "card_number". This field is encrypted in the history table but not in the gift_cards table. Let's say the passphrase is 'mypassphrase'. The following query takes about 1 second to execute with a fairly large amount of data in both tables:
SELECT max([history].[date_of_wash]) AS LastUse
FROM gift_cards AS gc LEFT JOIN history
ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50))
GROUP BY gc.card_number
When I use a declared variable to contain the passphrase, the same query takes over 40 seconds. For example,
declare @vchPassphrase as nvarchar(20)
select @vchPassphrase = 'mypassphrase'
SELECT max([history].[date_of_wash]) AS LastUse
FROM gift_cards AS gc LEFT JOIN history
ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50))
GROUP BY gc.card_number
This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it. Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster?
We are using an OLE DB Source for the Data Flow Source and OLE DB Destination for the Data Flow Destination. The amount of data being moved is about 30 million rows, and it is gather using a sql command. There is not other transformations in between straight from one to another. The flow starts amazingly fast but after 5 million rows it slows considerably. Wondered if anyone has experienced anything similar with large loads.
I have an SSIS Package which is designed to import log files. Basically, it loops through a directory, parses text from the log files, and dumps it to the database. The issue I'm having is not with the package reading the files, but when it attempts to write the information to the db. What I'm seeing is that it will hit a file, read 3000 some lines, convert them (using the Data Conversion component), and then "hang" when it tries to write it to the db.
I've run the SQL Server Profiler, and had originally thought that the issue had to do with the collation. I was seeing every char column with the word "collate" next to it. On the other hand, while looking at the Windows performance monitor, I see that the disk queue is maxed at 100% for about a minute after importing just one log file.
I'm not sure if this is due to the size of the db, and having to update a clustered index, or not.
The machine where this is all taking place has 2 arrays- both RAID 10. Each array is 600 GB, and consists of 8 disks. The SSIS package is being executed locally using BIDS.
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
I installed Visual Studio 2005 Professional then after that was installed and running, I tried to install the the SQL Server 2005 Developer's Edition which installed but I do not get the SQL Server 2005 Studio Manager. I have remove and reinstalled but it never gets installed. Any ideas?
We have been using Sql Server 2005 Compact Edition 3.1 RDA synchronization method successfully on Sql Server 2000 database. Recently we moved the database to Sql Server 2005, sync doesn't work anymore, it just hangs on one table. On further investigation, we found out that it's the index on that table that causes this. We removed the index, it works fine. We are wondering the root cause, removing the index is not a solution for us. Any thoughts?. Thanks.
(1) I have already installed of SQL Server 2005 Developer Edition first.
(1) Can I install visual studio 2008 without the 2005 express edition of SQL server? Will be any problems because I don't have express edition of SQL server? Do I need to install the express edition of SQL server as well?
(3) How to use SQL Server 2005 Developer Edition instance on visual studio 2008?
I am trying to install SQL 2005 in a 2-node virtual Windows 2003 cluster. I set the cluster up through Virtual Server 2005 with 2 virtual nodes and one virtual domain. The nodes can connect to each other as well as the physical machine. When I try to install a fresh copy of SQL 2005 on my cluster, I get an error every time. The error stops the installation while checking system configuration after installing prerequisites. The log file entry is as follows:
******************************************* Setup Consistency Check Report for Machine: --SERVERNAME-- ******************************************* Article: WMI Service Requirement, Result: CheckPassed Article: MSXML Requirement, Result: CheckPassed Article: Operating System Minimum Level Requirement, Result: CheckPassed Article: Operating System Service Pack Level Requirement, Result: CheckPassed Article: SQL Compatibility With Operating System, Result: CheckPassed Article: Minimum Hardware Requirement, Result: CheckPassed Article: IIS Feature Requirement, Result: Warning Description: IIS is not installed, therefore Report Server feature will be disabled Action: Install IIS in order for Report Server feature to be enabled Article: Pending Reboot Requirement, Result: CheckPassed Article: Performance Monitor Counter Requirement, Result: CheckPassed Article: Default Installation Path Permission Requirement, Result: CheckPassed Article: Internet Explorer Requirement, Result: CheckPassed Article: Check COM+ Catalogue, Result: CheckPassed Article: ASP.Net Registration Requirement, Result: CheckPassed Article: Minimum MDAC Version Requirement, Result: CheckPassed <Func Name='PerformDetections'> 1 Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.1399.0 Error: Action "InvokeSqlSetupDllAction" threw an exception during execution. Error information reported during run: Source File Name: datastorecachedpropertycollection.cpp Compiler Timestamp: Fri Jul 29 01:13:49 2005 Function Name: CachedPropertyCollection::findProperty Source Line Number: 130 ---------------------------------------------------------- Failed to find property "ComputerList" {"SqlComputers", "", ""} in cache Source File Name: datastoreclusterinfocollector.cpp Compiler Timestamp: Fri Sep 16 13:20:12 2005 Function Name: ClusterInfoCollector::collectClusterVSInfo Source Line Number: 883 ---------------------------------------------------------- Failed to detect VS info due to datastore exception. Source File Name: datastoreclustergroupsproperties.cpp Compiler Timestamp: Fri Jul 29 01:13:49 2005 Function Name: ClusterGroupScope.SharedDisks Source Line Number: 56 ---------------------------------------------------------- Failed to find a cluster group that owned shared disk: J: WinException : 2 Error Code: 0x80070002 (2) Windows Error Text: The system cannot find the file specified. Source File Name: datastoreclustergroupsproperties.cpp Compiler Timestamp: Fri Jul 29 01:13:49 2005 Function Name: ClusterGroupScope.SharedDisks Source Line Number: 56
I have a SQL Server 2005 evaluation that has already been installed and setup on a server. I believe it originally had a 180 day eval. There have been numerous databases and users added as well as maintenance plan created...
The eval was put on the machine as an interim solution while waiting for paper work and order processiing things to happen. All the paperwork and ordering... have been completed and I now have the real SQL Server 2005 Standard Edition license key ....
I am very new to SQL Server and need to determine ...
1. Can I update the eval copy to become permanently licensed?
2. Would I want to upgrade the eval to permanent? Will I lose any capabilities by keeping the current eval setup?
If it is reasonable to keep the eval setup
3. How do I go about entering the license key to make it permanent?
If it is necessary to install the new Standard Edition...
4. How do I install it while maintaining the already defined databases, data, users, maintenance plan...
Hi, I am developing an application for pocket pc 2003 smart device which utilizes synchronization between SQL CE 2005 and SQL server 2005 by means of REMOTE DATA ACCESS method call PULL( ) and PUSH( ) but currently these are the details of the error Im encountering when PULL( ) method is called:
Error code: -2147467259 Descripion: An error occured on the computer running IIS.Restart the IIS server.
Minor error: 28022 Source: SQL server CE Currently Im developing this application as a final project and due to some limitations I have setup my pc to act as the SQL server and the IIS server both on the same machine.
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon
Do you now, How can I move database with datas to release version of sql server? Procedores backup/restore can't help becouse of unsuported version database.
How do I upgrade Sql Server 2005 Express to SP2 if I also have SQL Server 2005 Enterprise SP2 already on the same box? I installed Sql Server 2005 Enterprise first, then applied SP2, then installed Visual Studio 2008 (which installed SQL Server 2005 Express).
When I try to upgrade Sql Server 2005 Express to SP2 using the special Express edition of SP2, I get an error message that says "None of the selected features can be installed". I end up having to cancel the install of SP2.
I'm left with SQL Server 2005 at level 9.0.3054 and SQL Server 2005 Express at 9.0.3042. How can I bring SQL Server Express 2005 to the same level as SQL Server 2005 Entperprise?
Hello!! How can I upgrade from SQL Server 2005 Express Edition to SQL Server 2005 Enterprise Edition? I see allot of example of upgrading from SQL Server 2000 to SQL Server 2005, but no example on upgrading from SQL Server 2005 Express to SQL Server 2005 Enterprise or Standard. Is this possible?
I need to install SQL Server 2005 Standard Edition on a new Windows Server 2003 machine that has SQL Server 2005 Express Edition installed by a backup application (Backup Exec). Will I need to uninstall the express edition in order to install the standard edition? Can the two coexist as separate instances or will the standard edition installation simply upgrade the express installation?
I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...
What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.
I first tried installing the DVD and of course it told me I must use the command prompt installation method to include the line SKUUPGRADE=1. I found good references to this in the following links:
My servername is URHCS63 and my instance is SQL2005DSS.
When I execute this command, a installation dialog box opens briefly and then disappears and nothing else happens. Here are the results of the bootstrap log file:
Microsoft SQL Server 2005 9.00.3042.00 ============================== OS Version : Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 1 (Build 3790) Time : Thu Oct 25 16:17:48 2007
URHCS63 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter. SQL Server Setup was interrupted. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
Hi all, I just installed my VS 2005 Pro (full installation) and it installed Sql Server Express 2005. I also have a CD for Sql Server 2005 Developer Edition which I have not installed yet. My need for Sql Server on my development machine is for building and testing apps - just myself - no users will be hitting my sql server on my PC. Most of the time I hit my web server's sql server but also want the engine on my PC for when I'm offline. Question: Should I install the developer edition and remove sql server express? Doesn't the developer edition more closely emulate the full-blown sql server, just that it only allows one or a few users to hit for development and testing purposes? At the same time, sql server express is scalled back in that it doesn't have all the goodies? Thanks! Stewart
Hi, My objective is using a single DB file (mdf file) + SQL Server Express 2005 to run my program I got two softwares here, Visual Studio 2005 and SQL Server 2005. Firstly, I installed Visual Studio 2005, normally the SQL Server 2005 Express Edition will be installed with this software. After finishing the installation. I opened a new project and try to open a DB file (.mdf) from Visual Studio 2005, it works. The problem is that I can't restore the mdf file from an old database file since it lose a lot of functions that appear in SQL Server 2000 / SQL Server 2005. And it is too troublesome and inconvenient to edit a DB file in Visual Stuio 2005. Therefore, I have to install SQL Server 2005 for me to edit the mdf file, so that I can modify, backup, or even restore the mdf file with another DB file. After the installation of SQL Server 2005, I got a tool, SQL Server Managment Studio, to edit the mdf file. I can open the mdf file but when I double-clicked on the mdf file in Visual Studio 2005, it gives me the following error message: Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251 It seems the SQL Server 2005 modify some of the files in SQL Server 2005 Express Edition. Is that SQL Server 2005 and SQL Server 2005 Express Edition cannot be exist at the same time? The issue is I'm working on my computer, but I dun want to install SQL Server 2005 in the server that I will transfer to. It will be much like using Access DB file, without the Microsoft Access software, the program can still work well. However, when I'm still in programming stage, I do hope to have a DB file as the source file for my program, but I can do some editing works with SQL Server 2005. Does anyone can give me a suggestion? Thanks for your kindly reply. Calvin
I am interested in installing both SQL Server 2005 Express and SQL Server2005 b2 on the same machine. Can they both be installed on the same machinewithout conflicts? As in two different instances?John Dalberg
I recently upgraded to SQL Server 2005. My databases are stable and functioning perfectly. However, these databases are using the 32-bit version of SQL Server. The servers are going to be upgraded to 64-bit processors and new Server 2003 64-bit OS's.
Everything I have been able to find says that it is a simple process of backing up the databases in the 32-bit environment and restoring them in the 64-bit environment.
Could it really be that easy? I am looking for someone who has done this to provide any "heads up" commentary on what to look out for during that process. Can anyone provide some information on this process?
Recently I have upgraded my system to SQL Server 2005 SP2 version 9.00.3054; I have developed my packages on SQL Server 2005 SP1 version 9.00.2047. I am unable to open all these packages on SQL Serever SP2 version 9.00.3054 it is giving me error.
Error: Error loading Package.dtsx cannot create an instance of the XML Document Object Model. MSXML may not be registered.
