Perfomance Question
Jun 13, 2006
I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.
I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.
Any suggestions
TIA
Laurence
View 2 Replies
ADVERTISEMENT
Dec 13, 2001
Hello,
I have a table with almost 2 mil records. There are two columns (Col1 int, Col2 int). Combination of them is unique. When i run a simple select statement like (select * from tblTableName Where Col1 = 5) with execution plan, cost is extremely high, even with PK(col1+col2). I tried to put clustered index on those columns but execution plan still shows "table scan" instead of "index seek". How can I speed this up ? I use that table mostly for search.
Thank you for help.
View 2 Replies
View Related
Aug 22, 2006
Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Thanks in Advance
Filson
View 1 Replies
View Related
Aug 26, 2006
Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Thanks in Advance
Filson
View 1 Replies
View Related
Dec 13, 2007
I have the next code to add 1260 records to SQL Server CE database on my device:
for (int nGroupNr = 1; nGroupNr <= 35; nGroupNr++)
{
myAdapter.Insert(null,(byte)nGroupNr,null,0,nSubGroupColor,false,false);
this.pbarCurrent.Value++;
for (int nArtNr = 1; nArtNr <= 35; nArtNr++)
{
myAdapter.Insert(nGroupNr, (byte)nArtNr, null, 0, nSubGroupColor, false, false);
this.pbarCurrent.Value++;
}
}
myAdapter is strong-typed DataAdapter generated by Visual Studio.
My database has just one simple table with 8 fields.
This code takes more than 5 minutes... to add 1260 records... is it normal or i'm missing something?
How can i optimise this operation?
Development enviroment:
Visual Studio 2008
.NET CF 3.5
SQL Server CE 3.5
Devices that were tested (.NET CF 3.5 & SQL Server CE 3.5 are installed):
Windows Mobile 5 emulator
Unitech PA500
Acer N300
View 5 Replies
View Related
Oct 29, 2007
;with cte as
(
select rank() over (partition by username order by guid ) as rank from MyTable where siteurl='myurl'
and VisitedDateTime between '2007.02.05' and '2007.09.30' and IsFiltered=0
group by username,guid
)
select count(rank) from cte where rank=2
this query is taking 6 seconds to execute .. 'MyTable' table is properly indexed..
1 million rows are returned by common table expression.. i want to reduce execution time of
this query to some milli seconds..please help me..
shipin
View 25 Replies
View Related
Jun 13, 2008
Iam using the below query to create a key using the column combinations and a seperator"%" to insert the same in to table my issue is with the perfomance of this query.
This query is returing aroung 6000 records and its taking 11 seconds to return the result.is there any way that i can optimize this query to improve perfomance.Select (Item.ItemCode +'%'+ Product.Name + '%' + Quantity.ID) as Key1 from
Items,Products,QualityPlease advice
View 4 Replies
View Related
Sep 20, 2001
Hello,
In a database here we have a table that is a list of codes. Many other tables in our database have foreign keys to this table (T_TYPE_CODE). The joined column is an integer that is a clustered index on the table.
One of the developers here says that in one of his queries he has to OUTER JOIN to this table 15 times and that the OUTER JOIN is killing performance. He wants to add a record to T_TYPE_CODE that will represent NULL so that any NULL values in the tables that foreign key to this table will use that ID instead of NULL. In this way he could use all INNER JOINs.
To me this seems like a bad idea - NULL is NULL and creating a value to represent NULL will open a whole can of worms.
My question: Is there a performance hit for using OUTER JOINs against this table, considering that the join is on a single column and is a clustered, unique index?
Also, what problems can we expect to run into if we use a
Thanks!
-Tom.
View 1 Replies
View Related
Mar 6, 2001
Hello everybody,
We have a web site with about 100 connections per hour.
This site is only for consultation : read only.
We implement ASP on IIS 4.0 and use as often as possible stored procedures with ADO objects on client side (ASP).
We close every objects after they are used.
The site works about 1 hour and then is stopped because inetinfo service is down.
The memory and the CPU on the SQL server machine are quiet at maximum.
'user connections' is to 0 (so 32767 enabled connections).
'locks' is to 0 (so it will give memory dynamically).
The machines are very powerfull : 2 X bi-processor P III 700 Mgz, 512 Mo RAM :
1 for the front-end, the other for the SQL Server.
We think that we can make it work with such a materials.
Thank you very much.
Laurent Diep.
View 2 Replies
View Related
Jan 20, 2004
Can someone tell me if it is possible to see the drives on the server using Perfomance Monitor? I so, where are tehy hiding because i struggled the wholed day!
View 6 Replies
View Related
May 9, 2008
Hi all
We recently upgraded from sql 2000 to sql server 2005. Our system was developed using Microsoft visual Basic.
Since we upgraded to sql server 2005 our system has been very slow. I suspect that it was because of the new sql 2005 installation that i made.Does anyone know how to solve this problem for me to be able to increase the perfomance speeed of our system again.
It has been stressing me for a while now.
Pliz help
Thanks!
View 4 Replies
View Related
Feb 21, 2008
Hi,
In my cube, I used Time Intelligence wizard to create calculated members such as YearToYearGrowth and YearToYear Growth%. But when I try to pull those two items in RS 2005 for more detailed levels, the performance of running MDX dataset is terrible even though my fact table has only 36234 rows of data and I have 6 dimensions (the largest dimension includes 37801 rows).
One thing that I did was removing ALLMEMBERS keywords in the MDX designer but I didn€™t improve much the time it takes to execute a query. Right now, it takes 1.5 minutes to execute may MDX query in RS 2005. Is there a way to improve the performance of MDX query (generated in RS) in RS 2005?
Thanks!
View 1 Replies
View Related
Oct 19, 2007
i have a table with 5 million rows (6 months data) .. every month approximately 1 million data is getting inserted..(not
bulk insert) ..table is properly indexed.. There is a stored procedure to generate report based on this table.. (only this table no joins) .. stored procedure do lot of permutations (includes lot of temporary tables ,group by having,count() etc)..
it takes 2 minutes to generate the report.. i want the report to be generated with out taking a second..
should i partition this table??? size of table is 2gb.. i dont know whether this table is a right candidate for partitioning or not.. please help me..
Thanks and regards
shipin
View 3 Replies
View Related
Jul 20, 2005
Dear ReaderI am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.I am able to identify the best candidate for the indexing.Below is the details I want to understand:AreaZIPCityCountyDistrictState/ProvinceCountryNow I want the data retrival optimization through Index. (you can suggest another idea, also)Entities Area,...., Country have independent tables.Example:Area_TableAreaID (PK)AreaThey have relationship- one to many- if you go from Country to Area.There is one more table:Location_Table (PK)LocationIDAreaIDZIPIDCityIDCountyIDDistrictIDState/ProvinceIDCountryID(Location_ID is further related to the Address of the contact.)GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.& simultaniously Location_Table is also being inserted with the details.Following is the situation of being queried these tables:(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)(2) Contacts have to be retrived on the basis of Area, ZIP, .....County. (Necessary Groupings are required )Example:If Contacts are queried Country Wise then the Display should beCountry1State1District1County1City1ZIP1Area1Area2ZIP2City2County2District2Country2Please Guide.SuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15*****************************************
View 5 Replies
View Related
Nov 29, 2007
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:
declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)---
(I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
View 8 Replies
View Related
