We just installed SQL Server version 800.194 on a dual processor
server equipped with a gigabyte of RAM, running Windows 2000 Server
operating system. We set up a few databases with (so far) very tiny
tables.
When I am working locally (i.e. on the server itself) with Query
Analyzer, even the simplest operation is incredibly slow. If I bring
up Windows Task Manager looking at the Processes pane (Query Analyzer
shows up as "isqlw.exe"), and use "View/Select Columns..." to choose
"I/O Writes" and "I/O Write Bytes", then I observe that doing a
select* on a table with a single row results in over 500 "I/O Writes"
and 170,000 "I/O Write Bytes" of data written. It requires 15 seconds
to return the single row of information.
Even clicking on the Change Database Listbox results in hundreds of
write operations!
However, when I am working remotely with Query Analyzer, the select*
works perfectly normally. Neither "I/O Writes" nor "I/O Write Bytes"
are recorded.
I figure maybe there is some sort of security logging turned on that
records everything you do...
Whatever is going on here, how do you turn it off?
I have a database that's 2.5GB but only has about 17MB of actual data. I've setup a standby server that I load my dumps into. The load takes about 10 miuntes. The dump takes about a minute and a half (which also seems slow to me for that small amount of data). I don't expect that it should take that long to load 8800 pages into a database. The standby server is the same hardware as the production server (sinlge 500MHz Xeon, 2GB RAM, RAID 5). The server has only a single RAID 5 array to store all the OS, and all the SQL data however, I still don't thinkit should take thta long to load. Let me know what you think.
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.
I have designed a 22 table database in sql server that is to act as a backup/alternate access to the data we have stored in an ADABAS database. I've also written a vb.net console program that will take data from ADABAS through a broker connection (one row at a time), checks the sql server database to see if that information is already stored, and then either performs an insert or an update.
I can write the rows from ADABAS to a text file (not using the broker), at the rate of about 1.3 million rows in 1.3 hours. Data can be imported (I'm not sure how this import is done, possibly via a CSV file. SELECTS/UPDATES are not done, just INSERTS) at about 1 million or so an hour. But when I do the update, receiving information via the broker from ADABAS to the VB program (with it's SELECT, then UPDATE/INSERT), I'm only doing about 20-25 thousand rows an hour.
I ran a trace using the SQL Server Analyzer on the database while running the update program, and then ran Profiler using the generated workload. It created a few indices, but I just restarted the update program (I'm still developing, so I delete all rows from all tables each time I rerun the update), but I haven't seen that it's really any faster.
I have a rather large set of data to transfer over, and this 20-25 thousand row time is not nearly fast enough.
I have a database that had worked under msde 1.0 until reached the 2GBof dimension and the dB was blocked. To make possible the work i haddeleted old data from some table. The database restart, but theanswers from server become very slow. So i decided to pass to SqlServer 2000 without success. May i perform a check of this database?if is a indexs problem there is a way to rebuild them?Thanks in advanceAndy Wet
I just found out the response time of open a connection or execute a SQL command over VPN is very slow. It takes around 150ms for each round trip. If the same program run on LAN, it takes less than 1ms. I understand that VPN may have encryption and thus have a bit delay. However, if the delay happens whethever I make a SQL call, it will be unacceptable. Is there anything that I have missed out? If the delay occurs once only, it will be still great. (I think this is the point of connection pooling. Right?) However, it's really bad if the delay occurs each time I call SQL. Please help!
Database Restore takes much longer on Windows 2003 64-bit than on 32-bit... Is this simply the Service Pack level or does it have to do with the 64-bit/32-bit issue?
We have a Development/QA/Production environment setup in this manner:
DEV - fast restores - (about 2 hours) OS: Windows Server 2003 R2 Service Pack 2 DB: SQL 2000 Service Pack 4 (32-bit)
QA - slow restores - (about 10 hours) OS: Windows Server 2003 x64 Service Pack 1 DB: SQL 2000 Service Pack 4 (32-bit) Production - slow restores - (about 10 hours)OS: Windows Server 2003 x64 Service Pack 1 DB: SQL 2000 Service Pack 4 (32-bit)
I have a portal site that has many iframes loading various pages. One of the iframes requires data from a database that has a slow connection and right now there is nothing we can do about the slow connection and is something we have to live with. What seems to be happening though is that even though each page is loading seperatly in an iframe, when the page loads with the slow connection, it seems to hold up processing on the server for the other frames until the connection has been established with the server. It can be something like 10 seconds. I am guessing trying to establish the connection is holding up the worker process on IIS??? So I am trying to find a workaround bearing in mind there is nothing we can do about the slow connection for the time being? Does anyone have any suggestions? One I am thinking of is forcing this frame to load last so at least the other frames are not being held up. Another is maybe to use a seperate thread, but does anyone have any idea on this? Thanks in advance
I'm querying a small SQL2005 database and finding that the query can sometimes complete in under a second and then 5 minutes later the same query can take 15 minutes to complete.
The query I'm running is very simple as follows: select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId
The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.
The database was originally on SQL 2000. It is the only database on this powerful quad core server. The SQL Server CPU usage never goes above 40%, and always has free memory. No sign of locks.
I can't figure out why such a small database is going so slow with such a simple query. Any ideas?
I am developing a mobile 5.0 application. I use mobile Sql as the database in the pda.
In the program, i use dataset.xsd to create the table and tableAdapter, but the performance is very slow for just access the data from the database. It takes about 4200ms for just
this.userAdapter = new PDA_USERTableAdapter(); MBDB2DataSet.PDA_USERDataTable ut = userAdapter.GetUser();
the "new PDA_USERTableAdapter()" is very fast.But...
The userAdapter.GetUser() will only return about 20 rows, each rows only contains 5 field .But it cost 4200 ms for this line.
The sql statement in userAdapter.GetUser() is
SELECT User, PASSWORD, TYPE, USER_ID, Supervisor_ID FROM PDA_USER WHERE (TYPE = 5) ORDER BY User
Please Help, Urgent!!!!!
p.s (The total rows in the PDA_USER table is only 30 rows)
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected) Data access (selected) RPC (not selected) RPC Out (not selected) collation name connection timeout query timeout
I upgraded from 6.5 to 7.0 SP3. Now when I save (write) an invoice it takes about 10-12 seconds, at 6.5 it was 1-3 seconds. SQL Server and my Materials App are the only thing running on this box. This is the only area that has gotten slower everything else works great. I have 3 users saving invoices and about 15 people total using the system at one time. It's a compaq DL580 loaded with memory, database is 2,195MB in size. Same 6.5 client to access system as before. Should I rebuild/reindex the database? Is there something from the old 6.5 version I need to remove?? Thanks in advance!!!
I am asking this question on behalf of a friend. I have little knowledge of SQL 2005 but my friend is quite knowledgeable, although this is the first time he is dealing with large database for a client. So here's the story.
His client has a database containing 1.5 million books. Now he is setting up a website which will enable users to search books. Searching by ISBN is no problem as it only takes 1 seconds. The problem is, searching by Title takes more than 20seconds, which is unacceptable. My friend has only done smaller database and he just recently thought of implementing indexing and now looking for other ideas.
Each row contains book details such as Title, Author1, Author2, Author3, Publisher, Publication Date, ISBN, etc.
Can anyone who are more experienced in doing large database share with me some design ideas? His client is aiming for 8seconds or less.
My database was previously running on sql2000 with 2 gigs of RAM and 2 x 2.8ghz XEON processors, and was running pretty decently.
I've now upgraded to SQL2005, 8 gigs of RAM, and 1 Intel 5130 2ghz processor (supposed to be more CPU power than previously)
The problem is its now running very SLOW.
I have run a trace and I'm finding queries that used to take 50,000 reads are now taking 1.4 million reads (25x more) The system runs for a decent amount of time but then SLOWS down massively for awhile. I can't find any cause yet.
What could be causing this ? What steps can I take towards resolving this? I have ran Tara's isp_ALTER_INDEX to try and help, I'm not sure what else to do.
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?
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
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.
I am writing a data access web page, but I find that the excution speed is too slow. My data base is just a data table which have five columns: id, code, quantity, price and Date. The data base has about 45000 rows. When I use OSQL or Query function, speed is just fine.
Here is the main code which I think cause the speed slow:
string conn = ConfigurationSettings.AppSettings["connectionstring"]; SqlDataAdapter adapter_2 = new SqlDataAdapter("select * from table",conn); DataSet ds = new DataSet(); adapter_2.Fill (ds,"table"); DataTable YahooOrders = ds.Tables["YahooOrders"]; DataRow[] product = new DataRow[20000];
.......
foreach (string s in split) // actually the split here has only one string in it { product = table.Select ("code like '"+s+"%' and Date >='"+minDate+"' and Date <='"+table.Select("Date = Max(Date)")[0][1].ToString()+"'"); foreach(DataRow myRow in product) { int count = Convert.ToInt32(myRow[2]); itemQuantity = count + itemQuantity; revenue = Convert.ToDouble(myRow[3]) * count + revenue;
// get product code, ignore repeated code int myIndex=code.BinarySearch( myRow[1] ); if ( myIndex < 0 ) code.Add(myRow[1]); } orderQuantity = product.Length + orderQuantity; } The first foreach actually excutes just one time, so it won' t cause any speed problem. The second foreach' s job is to sum each column of specified rows which is product here. So, any ideas about this? Thanks!
Actually from the application the developers are using count(column) to know the no. of rows resulted by a statement which joins many tables but its taking lot of time.
Is there an easy way to get the count of records(result set) of the output.
I cant use sysindexes b'z i need the count of the output genereted by the SQl Statement which joins many tables and retrieves many rows.
Got a problem (duh!). My MSSQL Server lags. Now, mind, it doesn't lag all the time. And it seems to be independent of the # of users trying to access the server. And it random clears itself up. And the problem doesn't present itself in SQL MGR, just on the web app we're running on it.
Setup: SQL Server 2k running on 2k3 w/ IIS & backup exec. All SQL data files are on a raid5 SCSI U160.
App: Intranet App developed by us for us. ASP.NET & VB.NET.
Symptoms: When queried server takes a LONG time to respond. So long infact it has become counter productive. When taking a look at the server, the CPU usage hovers between 50-75% and spikes up to 90% every now and then just for kicks. The memory usage is 2.35gb out of 4gb. To fix this we have to kill and restart all the SQL services.
Any thoughts on what to look at? There're indexes on the required FKs and the heavily queried columns. We're at a loss here.
A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?
I have 4000 record in my table employee. it takes 13 sec to get data. It this normal ? What is wrong ?
Thanks
Code Snippet
CREATE FUNCTION [dbo].[VrniStrukturo] (@id_sod int) RETURNS TABLE AS RETURN ( WITH tree(id, parent_id, naziv, nivo) AS ( -- Base case SELECT id, parent_id, naziv, 1 as nivo FROM employee WHERE id = @id_sod
UNION ALL
-- Recursive step SELECT e.id, e.parent_id, e.naziv, eh.nivo + 1 AS nivo FROM employee e INNER JOIN tree eh ON e.parent_id = eh.id )
--SELECT * SELECT id FROM tree --ORDER BY nivo, priimek, ime );
There re certian times when I want to execute a sql request (select for example) then It gets too long before I get an answer. (that happens only some times exceptionnaly). What does that mean, is it that somebody is using heavily the DB or may be using Entreprise manager or what exactly and how can I know who is responsible for taking all SQL server resources at that specefic time. What command or what tool can I use pls for this purpose. Thanks for your help.
Hello,Were using the data transfermation service to copy in an Ingres II 2.5database to an SQL Server 2000 database. Small databases don't present anissue, but when pulling one across that's about 20GB its been taking between12 and 24 hours. Both systems are relitively quick boxes and neither of themare tapped out on processor, disk I/O or network resources.I do have the "Boost SQL Server priority on Windows" checked under it'sproperties and all the processors are checked to be used.Does anyone know if there's a way to tweak SQL Server or Ingres to handlethis a little quicker? Or even an idea where the bottleneck could be may behelpful.Thanks,John.
I've got a performance question about a clr tvf that I have created. When I query the function it takes about 30 seconds for it to execute as apposed to < 6 seconds when I execute the same code in a console app (the 6 seconds includes outputting the returned data to the console, without writing the output to console it executes in about 1 second). Both the function and the app are iterating (>40,000) and returning ( >10,000) the same number of rows. I've noticed the following when viewing the executions in the PerfMon:
* the sqlclr tfv kicks the % Processor Time up to 30 for 30 seconds, the console app has % Processor Time at 9 for about 2 seconds * .NET CLR Memory - Allocated Bytes/sec spikes anywhere from 1 to 3 times during the sqlclr query at about 44MB/sec. It barely registers if at all when the console app runs. * In either case, % Time in GC is at zero.
I'm assuming that there are some configurations I'm ignorant of that can help me tune the execution. I can't imagine that it takes SqlServer that long just to iterate through the records.
I have recently decided to make the change from Microsoft access to SQL Server believing that it's a bigger faster beast with better parameterized queries and triggers and all that. BUT.I have some client data that I imported from their original paradox files.The invoice lineitem file contains over 1 milliion records.When I open this table in access and click show last record, the record is displayed in about 1 or 2 seconds.I used the upsize to SQL Server tool in Access to shift my data into SQL Server.When I use the Express Mangagement tool to open the same table and say show me the last record, it takes 17 minutes.I admit that most numeric data types have been translated to floats, so that's probably not good.But I cant alter them from floats to numeric or decimals using the table design tool.Do the conversion anomalies make up the whole reason why SQL Server seems so incredibly SLOW! ?????????
I am running the following BCP to extract a table with 156641604 rows.
bcp TestDB..data out test3.bcp -T -b1000000 -a32000
When running this i notice that the disk read bytessec counter in performance monitor on the drive that has the database devices is only reading 30mbsec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?
Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again. The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs. I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines ..... all with different sites, the only thing in common between them is SQL. thanks, -c
This sounds like a pretty easy one. I have a SQL 2000 database with 2-3.4GHZ CPUs and 1GB of RAM. I have one database on it. I go in Query Analyzer on another machine and run a simple query like 'SELECT * FROM USERS' which should return 15,000 rows.
IT takes 30 (thirty) seconds to finish this query. OMG
Where do I start to decipher why on Earth this takes more than .01 seconds?
I need urgent help, about someting: i've developed and deploy an aspnet web site (data works with sqlserver), but after a few minutes working with some users, the permormance slows and stop the site.
