Everything is flowing smoothly for the SQL Server Database I have, except one type of retrieval and that is when the where clause has a range of data values to do the retrieval then the performance is terrible. I cannot anticipate every range. There are indexes on the table to try to help; however, nothing seems to help. Has anyone had a similiar problem? Any suggestions to improve performance?
Thanks, Mary
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View 1 Replies View RelatedI've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select *
from dbo.ACCTING_TRANSACTION_hISTORY
where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and
ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
I have two tables:
Headers(id int, time datetime)
Data(id int, product_id int, property_id int, value float)
Data.id references Headers.id
Headers.id is a primary key,
Data has clustered index (id, product_id, property_id)
Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.
Right now I am doing
SELECT id, time, value
FROM Headers H, Data D
WHERE
H.id = D.id AND
H.time >= @StartTime AND
H.time <= @EndTime AND
D.product_id = @ProductID AND
d.property_id = @PropertyID
This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance
Hi,
I'm setting up my SQL Server 2005 instances to use dynamic ports on one of my machines. What is the tcp port range used? I need to know the range so I can open the correct ports on my firewalls.
Thanks
Brian
I had a pretty simple query like
select * from table_A
where email in (select email from table_B)
or
email not in (select email from table_c)
It ran for a very long time.
But if I ran the select with either of the conditions by itself it took just a second. Combining both conditions really slowed it down.
I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.
Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.
Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.
If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?
Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?
I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.
Hi There !!
To finetune performance for some of our queries,
I have come across suggestions to use
- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases
Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????
Also,
I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer
tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )
USING A WHERE CLAUSE and Alias
-------------------------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null
USING A JOIN
-----------------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL
******
I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:
Any comments/ suggestions.
Thanks,
Have a great time
-Ranjit.
-------------------------------------
It pays to be honest to your DBA
Consider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared
View 6 Replies View RelatedHi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
View 1 Replies View RelatedTry the following code in SQL Server 2005:
Code Snippet
CREATE DATABASE test_bug
GO
USE test_bug
GO
CREATE TABLE test1 ( a int )
CREATE TABLE test2 ( b int )
GO
INSERT INTO test1 VALUES ( 1 )
INSERT INTO test2 VALUES ( 2 )
GO
SELECT * FROM test1
SELECT * FROM test2
GO
-- This should error but instead returns all rows from test1
SELECT * FROM test1 WHERE a IN ( SELECT a FROM test2 )
I've tried it onw three different boxes that I have access to.
When is RTRIM needed in a Select ... where clause. I noticed that if I have a column named TEXT varchar(17) which is varchar and in the where clause I state where
TEXT = 'This is the text'
or I state
TEXT = 'This is the text ' followed by 4 spaces
The equate still works - so when do I need RTRIM?
Do I need RTRIM for a host variable:
...where TEXT = RTRIM(:VAR_001)
if the host variable is the same length as the TEXT column field in the SQL Server 2005 database?
Hi
Can we use Top Clause in the Select statement while using SQL Server 2005 Compact Edition. If not, is there any other workaround available to achieve same results.
Regards,
Salman Shehbaz.
Hi!
I have just installed Microsoft OLEDB provider for DB2 on a SQL server 2005. I created a Linked server against our DB2 V.8 Z/OS database. I tested the connection via execution of a simple select call, something like:
SELECT FIELD_A FROM DB2SRV.DB2DB.CREATOR_A.TABLE_A
WHERE FIELD_A='ABC'
I traced the call on the DB2 site. The SQL running on DB2 site was to my surprise without the €œwhere clause€?:
SELECT FIELD_A FROM DB2DB.CREATOR_A.TABLE_A
So, all rows are moved to SQL server before the where clause is executed, resulting in bad performance. The index on FIELD_A is not used and so on €¦ !
If anyone out there has an idea of what could be wrong, please let me know!
NB. I know that by using OPENQUERY pass-through query, all execution is done on the DB2 site.
Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId
FR
On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.
Best regards,
Ângelo Vilela
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
Hello!I have a very simple structured table:id | datawhere "data" is a varchar(100) This table would contain a lot rows (~ 500.000.000) and I want to select all "id" where data=@data. Is it realistic that the SQL Server could serve this request on a normal webserver within 1 or 2 seconds? Thanks!
View 1 Replies View RelatedI have recently upgraded from SQL Server 2000 to SQL Server 2005, and now all my queries run infinitely more slowly.
Here is the scenario - I run an extract of a MS SQL Server database at a client site, then recreate the database on our in-house server - but without indexes etc. Then I run various queries in order to created data files that will be used for importing into a global system. When I was running Server 2000, most of the queries ran in less than 10 seconds each, but under Server 2005 they take 3 minutes or more! Does anybody know of any parameters that I need to adjust to fix this problem?
We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.
Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.
When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.
But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().
We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?
FYI, here's the text of the article filter statement:
SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
end
)
)
And here's the statement we ran from Query Analyzer:
declare @id varchar(10)
select @id = 'aultnc'
SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
end
)
)
Thanks
Hi,I have a Microsoft SQL Server 2005 Enterprise installed on Windows Server 2003, and developing web application for 500 clients. So I am interested will I have any performance issues if I put in 'Articles' table, data for all 500 clients and then filter it on client ID, or should I make 500 'Articles' tables for every client one with different name and then change sqldatasource for gridview depending on which client is working on it. I will have, beside 'Articles' table, another 10 tables, which means 5500 tables total, if I use second approach, on first I will have only 11 tables. So I am asking is it better to have more tables with less data, or less tables with more data. And what are pros and cons for both approach. Thanks a lot!
View 1 Replies View RelatedIve got sql server 2005 WG edition running and have an access adp application which connects to it. However since upgrading to sql server 2005 from 2000 the adp project runs a lot slower. However when I install express on a machine and connect the adp project to it which sits on the same machine it runs just fine.
We have also rebuild all the indexes for the database but that doesnt fix the problem. Could someone please help...
I have one query which is calulating running total and taking just 6 mins to run on production SQL Server 2000 server but it is taking more than 45 mins to run on QA on SQL Server 2005 server. The index and data is same on both server, What other things we can check beside the index?
Thanks
Does using varchar in SQL Server 2005 significantly affect performance on updates?
Why or why not?
I have seen many SQL Server databases with many varchar columns - in other databases other than SQL Server it is advised not to use varchar because it significantly impacts performance.
I am trying to weigh when to waste space to help performance.
Hi,
I am having a table with 40 columns and it contains 4 million records. I got the results for one year in 40 secs. After tuning, it is retuning in 24 secs( what i have done is i created index on order by fields).
Can you please suggest me in which way I can increase the performance.
Note: I am using only one table with Primary Key.
Thanks
Dinesh
Hi
I'm not sure I chose the right forum, so any comments on that are also welcome
We recently changed from SQLserver2000 to SQLserver 2005 in the beginnen all went fine.
But now we are struggling with a severe performance problem...
suddenly SQLserver2005 reaches its max and is not longer able to work properly -> Extremely slow
I'm wondering if there are other people / companies / ... sharing this same issue?
Thanks for time and effort
Kind regards
Wimpos
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?
Server:
OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version5.2.3790 Service Pack 1 Build 3790
8 GB memory
Two Dual-core AMD Opteron 285 2.6GHz Processors
Server is not configured for AWE
Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF
SQL 2005
SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047)
Three instances installed on server - only one instance in use
Binaries and system databases on local mirrored disk
Database file (MDF) on one EMC LUN - dedicated physical drives
Log file (LDF) on one EMC LUN - dedicated physical drives
Query in question:
SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name,
Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber,
Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium,
Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON
Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name
Address table - 161,075 rows
Client table - 161,634 rows
Quote table - 59,145 rows
With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.
After changing maximum memory setting to 2048 MB, query runs in less than 1 second.
Question is:
What is the best practice for setting the minimum and maximum memory settings for SQL 2005?
What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?
Thanks
Hello everyone ,
I am looking for an useful sql server 2005 performance tuning book. i have been searching for a real nice book as i m going to start my job from next month in a financial domain with one of the requirement as sql server 2005 performance tuning.so i m looking forward a book which can help me doing well at my workplace. Any suggestions and links appreciated in advance .
Does anyone know of any documentation on the performance of partitionmerge/split? Does the merge or split of a partition cause any lockingon the partitioned table? If you were merging or splitting a largevolume of data rebalancing your partitioned table would youpotentially lock users out?
View 2 Replies View RelatedMy Performance Counters for SQL Server 2005 are corrupted. How do I repair them ?
Any help would be appreciated. Thanks.
Salyx
Specs
Windows 2003 Standard, AMD x64.
SQL Server 2005; x64; 9.00.3042.00; SP2 Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service
Pack 2)
This is a new install, so no "upgrade from SQL 2000".
This is a production server, so "reboot" is hopefully not part of the suggested repair.
Symptom
Open Performance Monitor. Open Add Counters. Open Dropdown "Performance Object". Instead of the SQL Server Performance Counter names, a list of 4-digit numbers appears. Other Performance Counters, eg, Processor, work as normal.
Attempted repair 1 - Recovery of system performance counters
Open Command Prompt
CD WindowsSystem32
lodctr /R
This failed to restore the full set of performance counters for an unknown reason.
Attempted repair 2 - Recovery from a backup file from a second host
I used the performance counter backup file from a second host which has an identical windows install. This properly restored the system performance counters, but failed to restore the SQL Server ones. This seems odd, because both system have - as much as I can tell - the same applications installed.
Open Command Prompt
CD WindowsSystem32
REM Load backup file from second host
lodctr /R:c:PerfStringBackup.INI
Attempted repair 3 - Recover SQL Server - specific counters
Open Command Prompt
CD WindowsSystem32
REM Load backup file from second host
lodctr /R:c:PerfStringBackup.INI
REM Clear and re-load MSSQLServer counters...
unlodctr MSSQLServer
lodctr "/R:C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlctr.ini"
Executing this pretty much wiped most performance counters. Only a small sub-set is now available.
More Info
SQL Server 2005 and later SP2 were installed under the administrator account.
MSSQLServer service runs under its own Windows Account (permission issues ??)
I get Event Log entries regarding x86 vs x64 Performance Counter Libraries. These, however, do not refer to ASP, not SQL Server.
I have 2 (virtually) identical hosts (same install sequence of apps). The Performance Counters on the second host work fine.
Exctrlst.exe lists MSSQLSERVER service, but I don't know how to diagnose the details.
Dear friends,
A simple question to you... witch of this dataflow destination is better for performance to write data in a SQL Server 2005:
1. SQL Server Destination?
2. OLEDB Destination?
3. Other?
Thanks!
Hi,
We have our SQL databases clustered using MSCS on X64 servers and are planning to apply SP2. During initial tests, we find that around 20-25% queries perform slow after applying SP2 compared to SP1. Just wanted to know if anyone else has found the same behavior and if there are any known patterns / issues with respect to performance for Sp2
I have a SQL Server 2005 database where covering indexes had to be used to improve performance for the heavy amounts of retrievals; however, the inserts into the tables are now very slow of course. Is there any way to improve the performance of the inserts without taking away the indexes.
Would changing locking or partitioning the index help the inserts?
Other databases use a concept of "freespace" to set up in the beginning - making pre-existing space for inserts - is there anything like this in SQL Server 2005?
Thanks for any help, Mary
We have updated to SQL Server 2005, let€™s say, in a hurry without thinking or testing. Databases were attached to the new instance of SQL Server 2005. It looked great when I tested it alone but then a new day come and as all users logged into the system we had got a big problem. The response times are very long and users receive time out errors all the time.
A little background:
The instance of SQL Server 2005 is installed on the same server as 2000 was installed on. 2000 has been uninstalled. It is a Xenon 3.2 GHz with 2GB RAM and SCSI raid. Data and logs are on different spins.
Application is an old ASP code and some parts are not optimized at all. But it worked fine on SQL Server 2000.
What could be the problem?
I really don€™t want to downgrade to SQL Server 2000.