Database Is Slow To Access After Being Idle For Some Time
Feb 10, 2007
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
View 4 Replies
ADVERTISEMENT
Mar 21, 2006
Is it possible to stop the server from going to sleep when it is idle to improve responsiveness to new connections?
View 21 Replies
View Related
Jan 26, 2000
I have a stored procedure which creates 3 temporary tables. Every table is about ten rows and 25 columns. The inserts in the tables goes fast (< 30 ms). The selects from them is also that fast. BUT the first select takes about 3200 ms one each of the temptables. (I first do insert, then select from them.) So the SP executes at about 13 seconds instead of 3.
Any suggestions, anyone, please?
View 1 Replies
View Related
Jan 29, 2008
Hello everybody,I have a ASP.NET (3.5) Web site which uses an data connection to my Sql Server 2005 Express. When I browse in my data-driven pages of my website, I am satisfied with the performance. But when I try my website after a long break, e.g. in the morning, the data-driven pages take a long time to come up, about 10 seconds. After this gap, when surfing on the other pages, there is no problem. It is only the first page. So it seems that my SQL Server goes "to sleep" after a certain time of idle.First I read a post about this problem which came up with the user instances of Sql Server. So I got rid of my user instance and set up my database "normally". But still I have that performance gap.I am using Linq To SQL and a couple of ListViews. I am totally confused how to manage this problem.Any suggestions?Thanks in advance,Hannes
View 1 Replies
View Related
Dec 7, 2007
I am using MS JDBC driver 2005 1.2 and in-house written connection pooling that was working fine for a number of years. This combination worked OK with SQLserver 2000. But recently we switched to SQLServer 2005 (x64) on WIN Server 2003 Standard x64 eddition. Everything seems work OK during business hours, however, after business hours when there are lesser users and connection stays idle for sometime, I am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.freeConnection(OnlineTransactionManager.java:420)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:707)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:688)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.finalize(OnlineTransactionManager.java:399)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)
Any help would be appreciated
thanks in advance
View 6 Replies
View Related
Feb 13, 2007
Hi, everyone!
I have this strange problem... After every time my application leaves sql-server idle (doesn't send anything, doesn't retrieve anything) next command to sql-server processes really long.
I've also noticed this bug/feature/misconfiguration even if I open a DB in Management Studio...
Please, could someone tell me, is there any timer that "puts a DB to sleep" if no one is using it for some time? Can I change the way server behaves in this situation?
View 4 Replies
View Related
Oct 5, 2004
I have a access database, the data store in another server. This noon, one of our user is runing the access database too slow. Open the database and search the data, etc. It took a long time to come out, Any body has experience on it, why, we had etrust install on each user machine, is that cause this too slow? Thanks in advance.
View 1 Replies
View Related
Dec 19, 2007
Hello All,
I’m looking for a solution to timeouts that occur when I’m executing a stored procedure from my web application. Most of the SPs will run from 3 to 15 minutes, and, unfortunately, modifying/optimizing them isn’t an option at the moment. I tried setting the CommandTimeout to 0 with no luck. Unless, I didn’t use it properly. Here’s my code: 1
2 try
3 {
4 string dbConn = ConfigurationManager.ConnectionStrings["ConStringNTMTLDEV"].ToString();
5 OleDbConnection connection = new OleDbConnection(dbConn);
6
7 lbl_SearchResult.Text = dbConn;
8
9 //OleDbDataAdapter adapter = new OleDbDataAdapter();
10 OleDbCommand cmd = new OleDbCommand("SP_CallHistoryLookUp", connection);
11 cmd.CommandType = CommandType.StoredProcedure;
12
13 cmd.Parameters.Add(new OleDbParameter("@phoneNumber", "1234567890"));
14 cmd.Parameters.Add(new OleDbParameter("@email", "123@123.com"));
15 cmd.Parameters.Add(new OleDbParameter("@WebUser", "123"));
16 connection.Open();
17 cmd.CommandTimeout = 0;
18 cmd.ExecuteNonQuery();
19 cmd.Dispose();
20 connection.Close();
21 }catch(OleDbException ex)
22 {
23 lbl_SearchResult.Text += "<br/> Something went wrong </br>";
24 lbl_SearchResult.Text += ex.Message.ToString();
25 }
26
27
28
Is it possible to launch a stored procedure and close the connection without waiting for a result?Would the stored procedure still run on the SQL server? I’m using MSSQL 7. Would you have any examples that would solve this problem? Thank you for your help.
R.
View 5 Replies
View Related
Jan 10, 2008
Hi there is there any way in tsql that I can tell when a database/table was last accessed? We have a lot of *** on our servers that need cleaning up.
If it is not possible could I put a trace on the servers that will tell me if any access has occured and run this for a couple of weeks for instance to see if any access has occured?
Thanks
Daz
View 3 Replies
View Related
Aug 23, 2004
Dear All,
I want my SQL database to be accessed always in a single user mode through my application. How can I do this ?
Thanks in advance.
Sushma
View 5 Replies
View Related
Aug 21, 1998
Hi
I have Problem, My response time is too Low. Is Any one Know how to improve my response time. My DATABASE SIZE IS 11 GB. I didn’t change any configuration parameter after installing SQL Server. Right my server Having default configuration parameters. Whether I have to change any parameters or not. My queries will generate lot of temporary tables.
Thanks
JK
View 2 Replies
View Related
Feb 10, 2000
We did an in place convertion of our data base from MS SQL Server 6.5 to 7.0.
Our application is much slower now on SQL 7.0. Any idea why?
The following is a sample SQL statement that runs quickly on SQL 6.5 and takes a long time on SQL 7.0 I also attached the query plans from SQL 6.5 and 7.0.
SELECT Person_Name.PerNam_Person_Name_PK ,
Person_Name.PerNam_Row_Status ,
Person_Name.PerNam_Last_Name_Sndx ,
Person_Name.PerNam_Last_Name ,
Person_Name.PerNam_Name_Suffix ,
Person_Name.PerNam_First_Name ,
Person_Name.PerNam_Name_Prefix ,
Person_Name.PerNam_Middle_Name ,
Person_Name.PerNam_Event_Person_FK ,
Event.Evn_Event_Nbr ,
Event.Evn_Event_Type ,
Event_Person.EvnPer_Last_Name ,
Event_Person.EvnPer_First_Name ,
Event_Person.EvnPer_Middle_Name ,
Event_Person.EvnPer_Name_Prefix ,
Event_Person.EvnPer_Name_Suffix
FROM Person_Name , Event , Event_Person
WHERE (Person_Name.PerNam_Agency_ID = "CL")
AND ( Person_Name.PerNam_Event_Person_FK = Event_Person.EvnPer_Event_Person_PK )
and ( Event_Person.EvnPer_Event_FK = Event.Evn_Event_PK )
and (Person_Name.PerNam_Person_Name_PK = 0 or ( Person_Name.PerNam_Event_Person_FK = 581541) )
and ( Person_Name.PerNam_Row_Status <> "D" )
Query plan in SQL 6.5
SQL Server Execution Times:
cpu time = 0 ms. elapsed time = 31250 ms.
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for REFORMATTING
FROM TABLE
Person_Name
Nested iteration
Index : PK_Person_Name
FROM TABLE
Person_Name
Nested iteration
Index : PerNam_Event_Person_FK
FROM TABLE
Person_Name
Nested iteration
Using Dynamic Index
FROM TABLE
Event_Person
Nested iteration
Index : PK_Event_Person
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
FROM TABLE
Worktable 1
Nested iteration
Table Scan
FROM TABLE
Event
Nested iteration
Index : PK_Event
SQL Server Parse and Compile Time:
cpu time = 0 ms.
Table: Person_Name scan count 2, logical reads: 6, physical reads: 5, read ahead reads: 0
Table: Event scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Event_Person scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Worktable scan count 0, logical reads: 0, physical reads: 0, read ahead reads: 0
Table: Worktable scan count 1, logical reads: 1, physical reads: 0, read ahead reads: 0
SQL Server Execution Times:
cpu time = 0 ms. elapsed time = 62 ms.
Query plan on SQL 7.0
SQL Server parse and compile time:
CPU time = 431 ms, elapsed time = 535 ms.
.................
Table 'Event'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0.
Table 'Event_Person'. Scan count 1, logical reads 6, physical reads 5, read-ahead reads 0.
Table 'Person_Name'. Scan count 1, logical reads 4588, physical reads 2, read-ahead reads 4092.
SQL Server Execution Times:
CPU time = 7921 ms, elapsed time = 13519 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 13781 ms.
View 2 Replies
View Related
Oct 27, 1998
We have an application that has about 100 users at a time. Roughly once a day, we experience a complete
slowdown on the server. All users notice it. The network seems fine because I can ping the server. Also,
I can attach to drives on the server quite fast so I don`t think it`s server resources. When I manage to
get in and do an sp_who, certain processes are blocking others. Talking to the users who were blocking,
they were not doing anything out of the ordinary - one was even doing just a select. The error log is full
of 17824 and 1608 errors. Is there some configuration setting that I should change? This is getting serious!
Thank you.
View 1 Replies
View Related
Jun 11, 2007
Hi, I have a query which returns normally in less than a couple of seconds. When coupled with a "TOP" statement however, the execution time changes in a way I can't explain.
When using "TOP 1000", the query returns in its normal time. Any top value greater than 10 seems to function normally. However, as soon as we hit this "magic number" the query begins taking 30+ seconds to return.
As far as we can see, there is nothing odd about this query or the data that it is searching, and this problem has us stumped! Can anyone explain why top is producing these results, and is there any optomisation changes that anyone can suggest to give us faster queries in these cases?
Many thanks, Andy J.
View 2 Replies
View Related
Apr 5, 2007
Hi
i had a problem with the SSIS response time - its very slow.
When I try to open the solution and than click to open the Pakage it's take at least 6-10 minutes until it's open.
Please advice what can be the reason
10x
View 4 Replies
View Related
Jun 26, 2015
Using SSMS 2012, we are experiencing extremely slow response times when opening SQL job steps to edit and also deploying SSIS Pkg's. Sysadmins have no problem. Users in the ssis_admin role have no problem. It's the rest of the users who have issues.
View 1 Replies
View Related
Nov 14, 2007
The following question might sound a bit stupid but I'm not a database expert so hopefully nobody minds me asking it.
Here's what I did:
1. I created an SSIS package that is supposed to import new data into my data warehouse as it becomes available.
2. Since I need to maintain some of the history I use the Slow Moving Dimensions part (set the history flag on input fields) but run into an error condition while running the package. The message basically says that I'm about to create a duplicate record which is not allowed.
Original Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
-----------
Now I enhanced it like this:
Extended Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
CreateDate (new)
NewDate (new)
ActiveFlag (new)
_____________________
Now on some records the package is supposed to archive history by populating the (new) fields. In order to keep the record unique (primary key constraint) thought, do I need to make the (new) fields primary keys as well?
So I guess I'm struggling with a more basic concept;)
I would appreciate if somebody could shed some light on this.
Thanks in advance.
Dirk
View 3 Replies
View Related
May 23, 2008
The SSRS website is slow when accessing it the first time after a period of inactivity (30 min. or longer). What could be causing this and how can I make it fast all the time.
View 1 Replies
View Related
Apr 29, 2008
Hi,
I have a problem with querying systemjobhistory data. Response time is slow and it is vary from time to time, sometime it takes few seconds and sometime it takes more than 2 minutes. I understand that there is quite a number of jobs in DB server and which might result in slow response time.
Is it possible to shorten the response time? like using index? My application is always look like hang when the query take very long time to run.
Any help is greatly appreciated. Thank you.
Regards,
maxChin
View 4 Replies
View Related
Jan 29, 2007
I am running SQL 2005 reporting services on a windows 2003 machine with SharePoint over SSL
My reporting services web directories are virtual directories under the SharePoint website.
I am having a problem with loading https://sharepoint.mysite.com/Reports/Pages/Folder.aspx
When you first open IE and bring up the page it takes about 30 seconds to load. Once you have connected via any machine, all other machines can connect at normal speed 1-2 seconds. All testing is being done on our LAN so there is no bandwidth issues when opening the pages.
My guess is that something is going into a sleep mode, but I am not sure where that would be set.
Thanks for any suggestions
Robert
View 6 Replies
View Related
Jan 28, 2002
I am experiencing VERY slow connectivity between client and server SQL 2000. I have checked the network activity and it is low. It also takes Enterprise manager ages to load, and then browsing tables and trees is impossible!
HELP!!
View 1 Replies
View Related
Feb 22, 2008
Has anyone else exported data to Access? If so, is it a slow process in SQL Server 2005?
I am exporting 3,000 records to an Access database using a view and it has taken 20 minutes. I am using the Export Data wizard. Any tips on how to speed it up would be appreciated.
View 7 Replies
View Related
Sep 17, 2007
I have 25+ WinXP SP2 workstations on a Novell file server (Novell login) and also have a W2K SQL 2000 Server for our database. We are usint Win NT authentication to the SQL Server.
One AND ONLY ONE user is having trouble with this setup. Her workstation is PAINFULLY slow accessing the SQL server through my Delphi programs, through our third party programs, and through ODBC connections.
I thought it was her workstation until I configured a clean workstation for her, and it too had the same problem. I then took a brand new Dell 9200 workstation with 2gb memory, gigabit NIC, etc. and configured it to use our servers. It too had the same slowness connecting to the SQL server. Everything else is fast.
I deleted her entry in the NT domain users table, and re-entered her. No help.
Can anyone shed any light on this?
View 3 Replies
View Related
Aug 3, 2007
Hii,
A bit new to SQL 2k5 but here goes, I recently installed SQL 2k5 on a preety decent box with about 4GB Ram and created a database called PointOfSale. As probably obvious by now, the application that accesses the Database is a PointOfSale application.
The store sells ladies clothing items, of which each is barcoded.
When an item is scanned via the barcode reader into the application, it takes like about 10-15 seconds for the item information to appear. Of course the item information resides on the database.
There are three registers and the same thing happens on all. I am running sp 2 for sql2k5 already.
Any thing I can do that would reduce the 15 sec delay significantly?
Any help at all would be appreciated immensley.
Regards,
Burt.
View 2 Replies
View Related
Nov 15, 2006
I have one aspx page that is very slow loading it is connected to SQL 2005 database. I am looking for any preformance tips out there
View 3 Replies
View Related
Aug 27, 2001
We have several people accessing the SQL server 7.0 data thru' MS Access Server has been slow on queries etc.Everything looks good,other than this one finding of MS Access being used for update/select. Is MS Access a potential problem? How to solve the problem of slow response time?
Note: MS Access is running on the client desktops retrieving data from SQL Server and joining data from MS Access to SQL Server.
View 2 Replies
View Related
Jan 9, 2006
Hi,
I have migrated my app from VB to VB.Net. A 3-tier app with remoting and COM+.
I am experiencing a long wait time of about 3 times higher than what it would take in the VB App.
I am using DataAdapter.FiLL method to fill the datatable.
I have tried.
Using DataReader ( Made the things worse )
Using BeginLoadData and EndLoadData
Creating a Dataset and calling fill with the dataset so that the round trip to the middletier is saved to bring the SQL.
But i feel now that whatever is done. the problem is with the fill method only?
Is there any alternative?
Please suggest. It is one of the most important thing which if not possible may lead to scrapping up idea of upgrading to .Net.
Shri
View 10 Replies
View Related
Jun 27, 2006
We have an issue with accessing SQL Server 2000 where the access of data from the database is slow unless the user is logged in as an administrator to their computer.
The system is as follows: SQL Server 2000 on a W2K server. Users logging into a Win 2003 domain server. Users using W2K on their workstations. Application is VB.NET using the Enterprise Library Data Block, connection pooling ON, and windows authentication.
We are assuming that the issue is down to one of authentication and that when a user is set as an administrator then they have instant access. We have been able to replicate the issue using just SQL server on a W2K workstation and accessing from another W2K workstation. Again data access is way slow unless the account is an administrator.
Glad of any ideas folks!
Kind Regards
Ian Logan
View 10 Replies
View Related
Feb 1, 2008
I have a table that has appx 3.2 million rows. see sp_help
Name Owner Type Created_datetime
-------------------------------------------------------------------
TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893
Column_name Type Comp Lngth Prec Scale Nullable
------------------------------------------------------------------------
RowID int no 4 10 0 no
SPHInstID int no 4 10 0 no
BPDInstID int no 4 10 0 no
BMUID varchar no 11 no
InfoImblCfw numeric no 9 12 2 no
BMUPrdNonDel numeric no 9 12 2 no
PrdFPN numeric no 9 13 3 no
PrdBMUBalSrvVol numeric no 9 13 3 no
PrdInfoImblVol numeric no 9 13 3 no
PrdExpdMtrVol numeric no 9 13 3 no
BMUMtrVol numeric no 9 13 3 no
PrdBMUNonDelBidVol numeric no 9 13 3 no
PrdBMUNonDelOfrVol numeric no 9 13 3 no
TranLossFctr numeric no 9 15 7 no
TranLossMtpl numeric no 9 15 7 no
TradUnitName varchar no 30 no
TotTrdUnitMtrVol numeric no 9 13 3 no
BMUAppBalSrvVol numeric no 9 13 3 no
DTCreated datetime no 8 yes
DTUpdated datetime no 8 yes
Identity Seed Inc Not Repl
-----------------------------------------
RowID 0 1 0
RowGUIDcol
-----------------------------
No rowguidcol column defined.
Data Located on File Group
==========================
PRIMARY
Index Name Decsription Keys
----------------------------------------------------------------------------
idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID
This table has 1 clustered index based on its own unique record ID and that of its parent table record
I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.
However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.
The same transaction is now taking 8 to 9 minutes.
I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop..
I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed.
DBCC CHECKTABLE returns the following but takes 6 and a half minutes
DBCC results for 'TB_SAAI014_BPD'.
There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No errors are shown
A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds
Can anyone please point me in the direction of things to check, try or repair.
Any help greatfully recieved.
Jinx1966
View 10 Replies
View Related
Dec 18, 2007
first sorry my english,
when i execute a query for the first time whith full text service from visual studio, show me the error 'server not responding' and when i execute this query for second time works perfectly.
View 1 Replies
View Related
Jul 5, 2006
Hi
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
View 3 Replies
View Related
Dec 19, 2006
I have an Access2000 ADP that I want to run under Access2007. The problem I have is that some forms take up to 45 seconds to open in Access2007! These are not complicated forms--just simple navigable reference forms like setting up transaction types etc. that are based on basic select statements like:
SELECT * FROM ArReceivableType
Where ArReceivableType is a reference table (less than 10 columns, all int or nvarchar(100) max) containing about 15 or 20 rows. They open instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid), object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk, sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) = N'dbo' and object_name(sofk.id) = N'FK_FaAssetTransactionWork_ArReceivableType_ArReceivableTypeId'
It looks like Access2007 is reading all of the constraints for the underlying table, including all foreign keys. My SQL database contains 1400+ tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000 works great for this enterprise app, but I really like the new Access2007 features (and I don't want to still be developing Access2000 apps in 2010).
View 3 Replies
View Related
Jun 6, 2006
Hi,
first time poster/newbie here.
I've
got a football (soccer for the yanks!) predictions league website that
is driven by and Access database. It basically calculates points
scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There
are two sections of the site however that have almost ground to halt
now that more users have registered throught the season. The players
section and league table section have gone progressively slower to load
throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All
the calculations are performed in the Access database Ive written and
there are Access SQL queries to get the data out.
My
question is, is how can I speed the bloody thing up! ! Somone has
alos suggested to me that I use stored procedures and SQL Server to
speed things up? Ive never used SQL Server before so I am bit scared
about using it (Im only a hobbyist), and I dont even know what a SP is
or does. How easy will it be upgrading the whole thing to SQL Server
and will it be worth the hassle, bearing in mind I expect my userbase
to keep growing? Do SP help speed things up significantly? Would
appreciate some advice!
Thanks in advance,
John.
View 1 Replies
View Related
