Hi,
I have SQL desktop version installed and for the last few days it has really slowed down. I have ran many anti-virus etc. and all is okay on that front.
Any tips regarding how I can tune things up? What should I look for and how do I go about it. Deleting LOG etc. etc???
Please guide.
Thanks.
hi here has a question for the mssql,
a problem occurs when i run a large database, the running speed is very slow.
for example, if i want to seek the record of 1500 employees with 15 per person(average) within 1 year(12 months), that mean i have to find record for 1500 * 15 * 12 time.
so, could i find a way to solve this problem? is this call tune the sql server/index/view?
what different of tune the sql server with index and view?
thanks for giving me advice.
:)
I have written a query which fetches data from a table with huge amount of data. This query is actually being used in a stored-procedure and it takes a lot of time, hence slows down my SP.
Here is the query that I'm builiding in the stored-procedure:
-------------------------------------------------------------
SET @selectLeadsInPeriodString = ' SELECT LM_Dealer, LM_Brand, SUM(LM_ImpressionCount)
FROM LM_ImpressionCount_Dealer'
IF(@timeCriterion IS NULL OR LTRIM(RTRIM(@timeCriterion)) = '' OR LTRIM(RTRIM(@timeCriterion)) = 'NULL')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE (CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) >= ''' +
CONVERT(varchar,@startDateTime,102) +
''' AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= ''' +
CONVERT(varchar,@endDateTime,102) + ''')'
ELSE IF(@timeCriterion = 'CurrentMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE())'
ELSE IF(@timeCriterion = 'PreviousMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE()) - 1'
ELSE IF(@timeCriterion = 'YearToDate')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE ([LM_ImpressionCount_Dealer].[LM_ImpressionDate] >= cast((''1/1/''+cast(year(getdate()) AS varchar(4))) AS datetime)
AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))'
IF(@brand IS NOT NULL AND LTRIM(RTRIM(@brand)) <> '')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' AND [LM_ImpressionCount_Dealer].[LM_Brand] = ''' + @brand + ''''
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString + ' GROUP BY LM_Dealer, LM_Brand'
The variables used in the query formation above are passed as input parameters to the SP.
The table being queried has columns 'LM_Dealer', 'LM_Brand', 'LM_ImpressionCount' and 'LM_ImpressionDate'.
Also, the table has a non-clustered index on the column LM_ImpressionDate.
With all this information, can anyone suggest as to how I can optimize the query above.
Thanks in advance.
-Dex
I think this is very silly question
but It is hard for me :eek:
SELECT email, host FROM WAITING_AUTH WHERE email NOT IN
(SELECT email FROM MEMBER)
AND host NOT IN (SELECT host FROM MEMBER)
thanks~ Have a nice weekend
If anyone is able to provide advice for tuning the below query in sqlserver, it is much appreciated. In addition any index suggestions arealso appreciated as I have access to the tables. Thank you.select a.id, isnull(b.advisement_satisfaction_yes, 0) asadvisement_satisfaction_yes,isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,casewhen isnull(b.advisement_satisfaction_yes, 0) >isnull(c.advisement_satisfaction_no, 0) then 'YES'when isnull(b.advisement_satisfaction_yes, 0) <isnull(c.advisement_satisfaction_no, 0) then 'NO'when isnull(b.advisement_satisfaction_yes, 0) =isnull(c.advisement_satisfaction_no, 0) then 'TIE'end as Satisfied_With_Advisementfrom aleft Join(select id, count(answer_text) as Advisement_Satisfaction_yes from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'yes'GROUP BY id) bon a.id = b.idleft join(select id, count(answer_text) as Advisement_Satisfaction_NO from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'NO'GROUP BY id) con a.id = b.idwhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'
View 2 Replies View RelatedENVIRONMENT:
I have SQL Server6.5 running under a dedicated NT Server. NT configuration includes dual pentium 200Mhz processors, 256MB RAM and RAID system.
The Database size is 1GB with actual data size about 500MB.
PROBLEM:
I have an application which uses lots of joins to get the results. My select query is running too slow even when I run it on the server.
I updated the statistics and rebuild all the indexes on the tables used by the query.
Any suggestions on using SQL Trace and tuning the server/database are welcome.
Srini
How can l trim the code and make the procedure run faster ???????????????
CREATE Procedure Disbursements_Cats
(@startdate datetime,@enddate datetime)
As
Begin
SELECT Transaction_Record.loan_No AS loan_no,
Transaction_Record.transaction_Date AS Transaction_Date,
Transaction_Record.transaction_type AS Transaction_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,
Product.product AS Product,
Product_Type.product_Type AS product_type,
Product_Type.loan_Type AS Loan_type,
Customer.first_name AS first_name,
Customer.initials AS initials,
Customer.second_name AS Second_Name,
Customer.surname AS surname,
Customer_identification.idno AS ID_No,
Bank.Bank_name AS Bank_Name,
Bank_detail.Account_no AS Account,
Bank_detail.Branch AS Branch
FROM Transaction_Record CROSS JOIN
Bank_detail CROSS JOIN Bank CROSS JOIN
Customer CROSS JOIN Product CROSS JOIN
Loan_Type CROSS JOIN Product_Type CROSS JOIN Customer_identification
End;
GO
Hi,
I have a query as mentioned below:
SELECT BillCurrencyID,DistD,Amount,PartnerFlag1
FROM Factsales_tab_Dtls (NOLOCK)
WHERE (sales_year =2007 OR sales_year=2008)
AND Country ='US'
1) Table Factsales_tab_Dtls is having more than 5.5 million of records.
2) It is having 32 columns.
3)There's nonclusetered index on columns sales_year & country
4)The query takes longer time for execution
Please help me fine-tune the query
Hello,
i need to tune some of my store procedures.
can any one help me how it's possible to tune particular store procedures?
Thanks
Prashant Hirani
hi,I have a problem asked by one of my senior person and finding theanswer .What is the step by step procedure for tune a large sql query.OR how do we tune a large SQL query with somany joins
View 6 Replies View RelatedHELP!!!I am trying to fine tune or rewrite my SELECT statement which has acombination of SUM and CASE statements. The values are accurate, butthe query is slow.BUSINESS RULE=============1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or bothhave a value.2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse theSQL from both Count1 and Count2)3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.SQL Code========SELECTSUM(CASEWHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR (FIELD_1 IS NOTNULL AND FIELD_2 IS NOT NULL))THEN 1ELSE 0END) AS Count1 ,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE 0END) AS Count2,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)END) AS Total_Count,SUM(CASEWHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)THEN 1ELSE 0END) AS None_Count,FROMTABLE_1
View 1 Replies View RelatedHi :I have a TableA with around 10 columns with varchar and numericdatatypesIt has 500 million records and its size is 999999999 KB. i believe itis kbi got this data after running sp_spaceused on it. The index_size wasalso pretty big in 6 digits.On looking at the tableAit didnot have any pks and hence no clustered index.It had other indicesIX_1 on ColAIX_2 on ColBIX_3 on ColCIX_4 on ColA, ColB and ColC put together.Queries performed in this table are very slow. I have been asked totune up this table.I know as much info as you.Data prior to 2004 can be archived into another table. I need to run aquery to find out how many records that is.I am thinking the following, but dont know if i am correct ?I need to add a new PK column (which will increase the size of thetableA) which will add a clustered index.Right now there are no clustered indices2. I would like help in understanding should i remove IX_1, IX_2, IX_3as they are all used in IX_4 anyway .3. I forget what the textbox is called on the index page. it is set to0 and can be set from 0 to 100. what would be a good value for it ?thank you.RS
View 8 Replies View Related
I've create a bunch of views to expose a logical model of the underlying database of an application server.
To enforce the security control, I've also created a CLR UDF to call the application server's API for security check and audit log.
For example, we have a table, tblSecret, and the view, vwSecret, is,
SELECT
Id,
ParentId,
Description,
SecretData
FROM tblSecret
WHERE udfExpensiveApiCall(Id) = 1
The udfExpensiveApiCall will return 1 if the current user is allowed to access the SecretData else 0. The CLR UDF call is very expensive in terms of execution time and resources required.
Currently, there are millions rows in the tblSecret.
My objective is to tune the view such that when the view is JOINed, the udfExpensiveApiCall will be called the least number of time.
SELECT
ParentId,
SecertData
FROM vwParent
LEFT JOIN vwSecret ON vwSecret.ParentId = vwParent.ParentId
WHERE vwParent.StartDate > '1/1/2008'
AND vwSecret.Description LIKE '%WHATEVER%'
Is there any way to specify the execution cost of the CLR UDF, udfExpensiveApiCall, such that the execution plan will call the UDF while it is absolutely necessary?
Is there any query hint will help?
Any recommendation?
Thanks,
Simon Chan
ASP.NET and MsSQL are run inside the same machine, and inside win2000 server,
and the physical memory limit of mssql is set to 192MB.
any one have any good idea(s)? please share to us here
:)
I want to tune the indexes on my database and I am trying to use the SQL Server Profiler to collect data for the Index tuning wizard to analyze. My question is what do I need to trace with the profiler so that the Index tuning wizard can work? I am looking at the trace properties in Profiler at the Events, Data Columns, and Filters tabs but I have no idea of what I need to capture.
Thanks in advance.
Mike
i have a report that runs on a huge table rpt.AgentMeasures , it has 10 months worth of data (150 million records as of today and will keep increasing). i have pasted my proc below , the other tables that are joined to this huge table do not have more then 3k records.This report will be accessed by multiple users (expecting 20 ppl). as of now this reports runs for 5 mins if i pull for 1 month worth of data. if it is wise to use temp tables.
ALTER proc [rpt].[Get_Metrics]
@MinDate DATETIME,
@MaxDate DATETIME,
@Medium Varchar(max),
@footPrint varchar(max),
[code]...
The 6:th of march Sql server data tools for Visual Studio 2012 was released.
[URL]
I seem to be unable to install this using the link provided on the blog page. I'm getting a "Same architecture installation" error. Running on the machine is Visual Studio 2012 Premium & Sql Server 2012 (64bit).
why I'm getting this installation error.
I am trying to install SQL Server 2000 on a Win2K OS machine but Iget this error message:"A previous program installation created pending file operations onthe installation machine. You must restart the computer before runningsetup."I see others have had this problem but have fixed it using the solutionin:http://support.microsoft.com/defaul...kb;en-us;312995I have tried the solution but to no avail. The registry key:HKEY_LOCAL_MACHINESYSTEMCurrentControlSetContro lSessionManagerPendingFileRenameOperations keeps reappearing as soon as Iclose RegEdit.Any ideas?Thanks,lq
View 3 Replies View RelatedPlease help,I am trying to install MSDE 2000 so that Backup exec will run on thisserver. I have gotten so far as to find an entry in my SQLSTP.TXT filethat says that c:mssql7 was detected so the install fails.Now, to start with there is no directory called C:mssql7 on thisserver. I have gone through all MS articles on manual removal of SQLserver and instances. I have searched the registry for mssql7 andremoved any keys that matched, as per MS article I have searched onSQLBOOT.dll and it is not present anywhere on my system and I still getthe same results.2:07:44 Begin Action: Detection12:07:44 SQL Server version already installed: 7.00.62312:07:44 Previous Install SQL Path: C:MSSQL712:07:44 Previous Install SQL Data Path: C:MSSQL712:07:44 End Action Detection12:07:44 Begin Action: ODBCLocked12:07:44 End Action ODBCLocked12:07:44 GetDefinition returned: 112:07:44 Previous SQL Server ProductType: MSDE [0x4]12:07:44 This combination of Package and Operating System allows a fullproduct install.can anyone think of anything here..what else could be causing the server to think there is a previousversion of SQL 7 on this box!!!?
View 1 Replies View RelatedI get an error when trying to install the SQL Native Client. It
says it cannot find the installation package and to use a valid copy of
'sqlncli.msi'. I've downloaded the Express setup twice and get
the same error from both of them.
I'm attempting this in a VMWare session setup for Windows 2K, Advanced
Server. I am trying to test my installation and setup
procedures. This is not a production setup, just install testing
and documentation. I have allocated only 272MB to the VMWare
session, which is why the system doesn't meet the recommended settings.
Any help would be greatly appreciated.
Below is the Summary.txt output file.
Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows 2000 Advanced Server Service Pack 4 (Build 2195)
Time : Wed May 03 09:54:23 2006
CPCWIN2KVM1 : The current system does not meet recommended hardware
requirements for this SQL Server release. For detailed hardware
requirements, see the readme file or SQL Server Books Online.
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.2047.00
Install : Successful
Log File : c:Program
FilesMicrosoft SQL Server90Setup
BootstrapLOGFilesSQLSetup0003_CPCWIN2KVM1_SQLSupport_1.log
--------------------------------------------------------------------------------
Machine : CPCWIN2KVM1
Product : SQL Native Client
Error : An
installation package for the product Microsoft SQL Server Native Client
cannot be found. Try the installation again using a valid copy of the
installation package 'sqlncli.msi'.
--------------------------------------------------------------------------------
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server Native Client
Product Version : 9.00.2047.00
Install : Failed
Log File : c:Program
FilesMicrosoft SQL Server90Setup
BootstrapLOGFilesSQLSetup0003_CPCWIN2KVM1_SQLNCLI_1.log
Last Action : InstallFinalize
Error String : An installation package for the
product Microsoft SQL Server Native Client cannot be found. Try the
installation again using a valid copy of the installation package
'sqlncli.msi'.
Error Number : 1706
--------------------------------------------------------------------------------
Machine : CPCWIN2KVM1
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.2047.00
Install : Successful
Log File : c:Program
FilesMicrosoft SQL Server90Setup
BootstrapLOGFilesSQLSetup0003_CPCWIN2KVM1_SqlWriter_1.log
--------------------------------------------------------------------------------
Machine : CPCWIN2KVM1
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install : Successful
Log File : c:Program
FilesMicrosoft SQL Server90Setup
BootstrapLOGFilesSQLSetup0003_CPCWIN2KVM1_MSXML6_1.log
--------------------------------------------------------------------------------
Hi every one,
I'd like to install SQL SERVER 2005 Express Edition ,I begin to install it from DVD but during the installation it dosn't ask me about any instance and after installation there isn't SQLExpress service and I can't use sql server .(there are not MSSQL and Binn directory in sql server 2005) but when I install visual studio 2005 and after that I install sql server 2005 it ask me for name of instance and with this way every thing is ok and I use sql server completely.
I 'd like to know the complete installation of sql server 2005 is depend on installation of visual studio 2005 befor it ?if not how can I introduce any instance after installation of sel server 2005 .
Hello,
Yesterday, to start SQLServer, I have used the DOS Command
setup SAPWD=xxx from C:sql2ksp3MSDE.
I think tne mix mode of authentification is correct.
But when I want to connect to the database by SQLEntMan, it doesn't work ...
How can I connect to SQLServer ?
Server adress = name of the computer
Username = ?
Password = password of windows ? password of of SAPWD ?
Thank you any help
Ben
Hi, I ama trying to install SQL Server Express 2005, when the setup.exe does an anlysis of the machine configuration it tellme that out of 10 checks one has failed, and that the machine needs tobe rebooted in order to fulfill a requirment from a previous installation, I did this and went to start the installation process again only ot be told that this check failure still exists, unfortunately I cannot progress, has anybody experienced this, I am using Windows XP Hone, with ASP.NET 2.0 installed and Web Dev Express 2005 Beta 2Looking forward to hearing fom you
View 3 Replies View RelatedI am trying the SQL 7 STANDARD installation on WIN`98. However, it creates the directory structure on the hard-disk alonwith files but on connecting to server it comes out with a VC++ assertion failed message.
The TCP/IP sockets are being used and the TCP/IP port (local ping) is working fine. Windows 98 was installed again and there are no prequisites for SQL 7 on 98 but the problem persists !
Anyone who has installed it on WIN`98 ?
TIA
Raghu
I have problems after installation in W98 of the Desktop.
In Enterprise Manager when I try to connect to the server gives the following error:
Assertion failed!
program c:windowsmmc.exe
file: ../src/sqlsspi.c
line: 119
Expression: Count
after I click on RETRY and gives the error:
a connection could not be establish to TEST05 - [SQL-DMO]code execution exception:
EXCEPTION_BREAKPOINT.
Please verify SQL SERVER is running and check your SQL Server registration properties and try again.
I check, and in Service Manager is TEST05 - MSSQLServer - running.
Also in Query Analyzer gives an error:
Assertion failed!
program c:mssql7innisqlw.exe
file: ../src/sqlsspi.c
line: 119
Expression: Count
after I click on RETRY and gives the error:
This program (ISQLW) has performed an ilegal operation and will be shutdown
ISQLW caused an exception 03H in module MSVCRT.dll at 0177:7800...
Where is the problem? From wrong installation or W98?
The installation CD was camming in the book :
"Special Edition" - Using SQL SERVER 7.0 from Stephen Wynkoop.
Please reply,
Tanks.
Hi:
We ran SQL Replication in Production SQL 7.0 with SP1. We encountered some problems. We ran the same tests in QA which used SQL 7.0 with SP1. The problems were gone.
Both Production and QA used SQL 7.0 with SP1 but they came from different resource CDs. Production one was installed from Microsoft Select CD in 1998. I did not recall the CD which was used for QA.
It indicated those two SQL 7.0 functioned differently and Production on had a bug in it. Can somebody to share with me a detecting tool and a criteria to find out where is wrong in the Production SQL 7.0?
Thank you.
Joan
Hi,
I am having pentium 3 with windows 98 I had Sql Server 7 installed But due to some problem i uninstalled it.
I am having difficulty in reinstalling the same i uninstalled completely and even removed entries in registry and tried to install But i am getting the same error always.
1)Acmsetup failed
2)setup failed to configure the server Refer to the server error logs and cnfgsvr,out for more information
There is nothing in server error logs
cnfgsvr.out file looks like this
Starting Service ...
An error occurred while attempting to start the service (1460)
SQL Server configuration failed.
What is ACMSETUP what it does how i can prevent its failure is the failure of ACMSETUP a reason for sqlserver set up failure
I appreciate ur replies
I have installed sqlserver 2000 (desktop edition) on my pc at home. I am running windows xp home edition. what i am finding is i am not able to log in to either Enterprise Manager or Query Analyzer. I have tried using different accounts during installation.
The service has started and the server seems to be running, but I cannot make a connection.
any suggestions,
thanks,
jim
We have installed SQL Server 6.5 in an NT system. We are able to start and stop the server from a client enterprise manager, but unable to connect to the databases there(ie., we are able to right-click on the server and start and stop the server but unable to right-click and connect to the server). Can anyone help us out with this?
View 1 Replies View RelatedHey guys, I'm new to this and had some general questions. I'm currently running windows xp pro with sql server 2005 tools installed. However I cannot deploy any of the reports due to not having IIS installed. I've now installed IIS however, do I need to completely uninstall SQL Server 2005 tools and is there a particular order to uninstall it in order to get everything to work?
As you can see I'm totally lost/new to this.
I tried to install sql server 2005 professional Edition in my machine.While doing the step by step installation, it was not showing the " Instance name " and " server accounts" ( i.e whether it is built-in system account or domain user account) slides and so finally i am not able to complete the installation. Kindly help me to rectify this. Thanks in advance.
View 1 Replies View RelatedHey Guys,Im going to install the SQL Server 2005,can any one tell me what are the things do i need to know before the installation.Its 64 bit and clustered.Active/passive
View 2 Replies View RelatedI am new with SQL and have a question regarding SQL Connections. I have 2 Win2003 Servers(tom and Jerry) setup with Microsoft Cluster installed in Active-->Active Mode. Also, on both of these servers, I have SQL 2000 installed in Active-->Active mode as well. So I installed SQL2000 on Tom first and gave the SQL server name of Dog1 which has and IP assigned to it, at the same time I gave an instance name of D1. So now my SQL on Tom server reads Dog1D1. I did another installation on Tom and gave it Dog2D2. Than I went and installed SQL again on Jerry, this time and gave name of CatC3, and CatC4.
Now in my DNS, I have Dog1, Dog2, Cat3, Cat4 defined with an IP I assigned.
Problem I am running into is I cannot connect my application or make and ODBC connection to Dog1D1, Dog2D2, Cat3C3 or Cat4C4. I am getting Server not found message.
Can anyone help me and tell me where I am going wrong, as I need to get this app up ASAP.
Thanks,
Jay