I have an ASP.NET application used in my university for student management. Any now and then the application throws an error like "DataBinding: 'System.Data.DataRowView' does not contain a property with the name XXXX" where XXX may be different. Of course that this properties exist and they are generated ok by the stored procedures.
After an recopilation everything seems to work nomally for a while.
I did not succeed in identifying the moment when the error is thrown but I suspect some reports in sql reporting services that lead to a server overload ??? that also is strange because the server specifications are quite ok, I think:
- OS: Windows 2003 R2, 64 bit
- SQL Server 2005 Standard, 64 bit
- 8 GB RAM
- Intel Xeon E 5345 2.33 Ghz (2 quad-core)
I would need help for:
- identifying the moment when this error occurs and _why_
- how could I repair it
- how could I distribute the load on more cores (from time to time Process Explorer from Sysinternals shows that only 1 core from 8 is working at full capacity)
Up until recently one of our SQL 7 databases has been running quite happily. However over the last 2 weeks the users have started to complain about performance levles, operations that would normally take 2-3 seconds now run for about 40 seconds. I have rebuilt all indexes and even ran update statistics althought both the 'Auto create Statistics' and 'Auto update statistics' are turned on. This failed to help so I ended up stopping and starting SQL server but still no luck.
I have run a DBCC SHOWCONTIG and the fragmentation is well within acceptable levels, scan desnity is at 94%. The only other thing I can think of is to reboot the NT server as it has been up for 67 days now. Can anyone else think of anything I might have overlooked?
Working with SQL Server 2000, I have a table with the following structure: ID (INT) userID (INT, foriegn key) productID (INT) productQTY (DECIMAL(5,2)) purchaseDate(smalldatetime)
I have about a 1000 users, entering about 20-30 rows per day each, i.e ~20,000 - 30,000 new rows per day. The table might be queried with a simple "SELECT" for the products a user ordered per day or per time frame (purchaseDate column). My question (finally) is - when should I expect to see performance degradation? Is there anything I can do to prevent it (i.e splitting this table somehow to several tables)?
Hello guys,Wonder if any of you could help me out here. I have just created annew empty database and imported data from another database into it.This was done with the import wizard from MMC.First thing that I noticed was the size difference, the old databasewas well over 1GB, but the new one was only about 400MB.Second thing I've noticed, and this is the problem, is that accessingthe new (smaller) database instead of the old one causes a huge speeddegradation, about 5 times slower than the old version.We are using MS SQL Server 7. Any help would be very gratefullyreceived.RegardsGethyn
We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.
I will try and explain the scenario.
We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?
I am experiencing performance problems with one of my stored procedures. When the stored procedure is first compiled an executed, it behaves as expected (it usually takes 1 or 2 seconds to complete). But its performace it is degradated, so in 1 day, it usually takes 120 seconds to complete !!!. Once the stored procedure is compiled, its performance it is then the expected.
It is a complex stored procedure with two integer parameters with only one select, but composed by multiple views and sub-queries. We have been trying to break the query into small pieces using temporary tables but without success. The SQL Profiler shows an unusual number of reads when it goes wrong (more than a million reads).
I think the problem is in the execution plan. I know than compiling the stored procedure, the problem is fixed, but I do not know exactly when and why it starts to happen.
The stored procedure is running under the following configuration:
- Microsoft SQL Server Standard Edition (64-bit). - Version: 9.00.1399.06 - RAM 16 MB - 8 CPUs
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
We have the same application installed on a few different environments with similar servers and similar hardward. The only difference is the versions of SQL and the colations. Is SQL 2005 a lot faster that SQL 2000? Could colation type make a big effect on performance? ScAndal
HiI want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.Inside the loop i am opening the connection and closing after use. The sample code is belowfor(int i=0;i<1000;i++){ sqlCmd.CommandText = "ProcName"; sqlCmd.Connection = sqlCon; sqlCmd.Connection.Open(): sqlCmd.ExecuteNonQuery(); sqlCmd.Connection.Close(); } What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?Please clarify me these question.. Thanks in advance.
this line 'select * from [viewUserLatestFee]' executes instantly (in Query Analiser) this line 'select * from [viewUserLatestFee] where orgID = 1' takes up to 30 seconds for 1000 rows (still in Query analiser)
can anyone please help - I seem to have ran out of ideas
I have a feeling people might be curious about the view so here it is:
We used a stored proc to pull totals from a database. Everything was fine until the table grew and started to time out. So we created a temp table to populate with a range of data and then pull the totals from there. Everything was fine until the table grew and started to time out. Any suggestion?
I am newly joined as SQL DBA. I want to check the Physical disk Performance. we have RAID 5 with 5+1 disks. I calculated NO Of IO's Per Disk. But how do we know what is actual limit of IO's per disk.
What's my best bet in getting better performance out of one of my database servers? Currently we have 1 set of Raid5 disks partitioned into 2 drives. This houses everything (system, database, and logs) If that server has 2 slots left for drives I was thinking of putting 2 mirrored drives and getting the logs off the main database space? (Make sense?) This is a vendored application so working with new indexes etc. isn't something I should do wo/ the vendor's interaction. Will what I describe above help?
We have SQL Server running on a dual processor Pentium 500mhz server. Our database is hit by about 300 users. 200 of those users are doing constant searches though a client table of about 250,000 records, which in turn is linked to a history table containing over 5,000,000 records. This is only the tip of the iceberg, we have many triggers, procedures, updates, etc. going in the background. The database has over 500 tables.
Keep in mind, these searches that are taking place can involve all kinds of fields: phone number, company name, fax number, first name, last name, status, wildcard searches, etc. So as you can imagine, the database is being hit with all kinds of funky requests to find records. I will be the first to admit that our developers (vendor) are not the best code writers, and we have a tough time getting them to optimize something they do not even understand themselves.
As I speak, our processor utilization is maxing out between 95 to 100 percent. I've done a lot of performance tuning and all of the problems lie in the searching. We've built, tested, rebuilt, re-tested each and every index. I even used the Profiler to filter what I could. It has improved, but our database is growing at a rate of 10 megs a day (already close to 3 gigs, not that huge). I think I've optimized my indexes as best as I can considering all the fields and possibilities available to users to search for records.
For a database that requires all of these different search criteria, what would be a more optimal server? We are looking to purchase something ASAP. I could really use help from someone in a similar situation. It seems odd, in mind, that a company of 300 people would need to rely on a quad server (four processor capability.).
HI I have 700 to 900 mb of production database , 2 gb of ram , 30 gb hard disk, My production machine is runnng very slow , i have check everything memory, page/sec, catch hit ratin , dbcc dbreindex but still it performance is not up to the mark. If i stop SQL SERVER & restart for few days machine works fine but after that again same thing it work very slow, what could be the reason if any one had any solution please suggest. Thanks Nil
Hi friends, My company has aution web site, it is written in Java and all sql statements generated dynamically. No stored procedures used. If 30 users uses this site it is OK but if around 300 users uses then the site becomes very slow(almost dead) and developers saying that database is the bottle neck. Please help me in this problem how can I check and overcome this problem.
We have recently upgraded to SQL 7.0 on NT 4.0/sp6 box which has got 4 PIII 700 processors, 1GB RAM, and 70GB HDD on RAID 1 and RAID 5. We feel that the application performance is not great as expected in SS7. (The application was running in 6.5 smoothly and performance was good)
Is there any option needs to set to improve performance? Now, SS 7 using all the 4 processors and dynamically allocated memory, etc. Any thoughts greatly appreciated.
I'm running MS SQL Server on a 1.4 GHz AMD Athlon Processor with 750 MB or RAM and ample disk space. I have a table with 14 columns; 2 datetime, 8 int and the rest are varchar of various sizes less than 13.
I run a java process on another machine that connects to the database and insert records. It takes about 6 minutes to insert 100,000 records.
I run the xp performance monitor and only about 25% of the SQL Server machine's cpu is being used. I run top on the Linux box running java and I see about the same results. Neither machine is kept busy processing. Why don't I get better performance? Could my local area network be that slow? How many inserts per minutes is good performance?
Does anyone know the performance differences between returning data from SQL Server as XML vs. as a record set? We are about to dive into the For XML world full force, but we wanted to make sure that we are not heading for a performance nightmare.
Thanks for any insight on this. I'll try to look for white papers and do some testing in the meantime.
Declare Cursor for table A WHILE @@FETCH_STATUS = 0 Get values from other function based on some business logic. INSERT Into another table B (or) UPDATE to another table B END
I have to insert/update values to table B, one by one row. So, it is taking more time. Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.
1. where do we see the buffer cache hit ratio. can we set the buffer catche hit ratio manually. 2.In query execution plan we execute the query for performance issue.which parameters we check to take an action?
I build a query in SQL-server 2000 but i'm not happy with the performance, it takes about 15 minutes to execute the query (4 min INSERT and 11 min UPDATE). The table tbl_total has 3 million records and an index on Contract and Item, the table contracts has 1 million records and a key on Contract and Item. How can I speed up this query, is it for example possible to put an index on @table (internal table)?
INSERT INTO @table SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date) FROM tbl_total TOT WHERE EXISTS (SELECT 'X' FROM contracts CONT WHERE TOT.Contract = CONT.Contract AND TOT.Item = CONT.Item) GROUP BY TOT.Contract, TOT.Item
UPDATE contracts SET contracts.Change_date = TT.Change_date FROM contracts INNER JOIN @table TT On contracts.Contract = TT.Contract AND contracts.Item = TT.Item
i need help in gaining the performance of this query SELECT tblSuperClientFile.ClientRefNo, tblReferral.RefID, tblRail.RailDescr, tblReferral.SuperClientVendorID, tblVendor.VendorName AS Client, tblReferral.AssignedVendorID, tblReferral.ReferralDate, tblSpikeDate.DateCompleted AS PlanRevCompleted, tblReferral.CloseDate, tblCloseReason.CloseReason, tblBankruptcyInfo.BK_Filing_State, tblBankruptcyInfo.BK_Case_Number
INTO #PlanRev
FROM FNFBSDataMart.dbo.tblSpikeDate tblSpikeDate WITH (NOLOCK) INNER JOIN #ActiveBK ON tblSpikeDate.MasterID = #ActiveBK.MasterID AND tblSpikeDate.FID = 3160 AND tblSpikeDate.DateCompleted <= GetDate()-5 INNER JOIN FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK) ON tblReferral.RefID = tblSpikeDate.RefID AND tblReferral.ReferralDate >= GetDate()-180 AND tblReferral.AssignedVendorID NOT IN (188,1721) INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK) ON tblReferral.RefID = tblBankruptcyInfo.RefID AND #ActiveBK.bk_Case_Number = tblBankruptcyInfo.bk_Case_Number INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK) ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867) INNER JOIN FNFBSDataMart.dbo.tblRail tblRail WITH (NOLOCK) ON tblReferral.RailID = tblRail.RailID INNER JOIN FNFBSDataMart.dbo.tblVendor tblVendor WITH (NOLOCK) ON tblReferral.SuperClientVendorID = tblVendor.VendorID INNER JOIN FNFBSDataMart.dbo.tlkpState tlkpState WITH (NOLOCK) ON tblSuperClientFile.StateID = tlkpState.StateID AND (tblSuperClientFile.SuperClientVendorID <> 1820 OR tlkpState.Abbrev NOT IN ('AZ','AK','CA','HI','ID','NV','OR','TX','UT','WA')) LEFT OUTER JOIN FNFBSDataMart.dbo.tblCloseReason tblCloseReason WITH (NOLOCK) ON tblReferral.CloseReaID = tblCloseReason.CloseReaID
can anyone have a look at it and give me a feed back asap
