Hi,
The database on which I am working, have 220 stored procedures.
I have to find out the procedures those have parameter sniffing problems. How I can find out???
Have any methode to find out this parameter snifffing problem.
This seems to be a classic parameter-sniffing problem -- except that I can't seem to force the query to run with the correct plan. The proc below tries to just select using LIKE. When I use a literal string predicate, I get a nice, fast index seek. All of the other variants do an index scan -- of the wrong index.
What else can I do to force SQL Server to use the correct index?
DROP PROCEDURE test_like
GO
CREATE PROCEDURE test_like
@searchText nvarchar(64)
AS
-- Straight literal string search. Runs quickly with index seek on companies.searchbrand
SELECT companyId
FROM companies
WHERE searchbrand LIKE 'adv%'
-- Each of the queries below does an index scan on the primary key index on companies.companyId
-- instead of doing a seek on the correct index.
-- Search for proc parameter
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
-- Search for proc parameter, with hint
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
OPTION ( OPTIMIZE FOR (@searchText = 'adv%') )
-- Local variable
DECLARE @localText nvarchar(64)
SET @localText = @searchText
SELECT companyId
FROM companies
WHERE searchbrand LIKE @localText
GO
EXEC test_like 'adv%'
Hi,
Is somebody will give me some idea about the parameter sniffing?
What is parameter sniffing??
How to overcome with this problem???
I have one procedure that is causing this problem.
Plz give me some tips to modify this proc to overcome this problem
dbo.cm_test (
@fromdate datetime = '01/01/1900',
@thrudate datetime = '12/31/9999'
)
As
Begin
SET ANSI_WARNINGS OFF
set nocount on
declare @minln smallint,
@CID char(24),
@EID numeric(12,0),
@extractdate datetime
/* Begin gathering data */
select @extractdate = getdate()
/* Begin gathering data */
if exists (select * from tempdb..sysobjects where name like '#tmpWTClm%')
drop table #tmpWTClm
create table #tmpWTClm (
ClaimID char(24) not null,
EventID numeric(12,0) not null)
insert into #tmpWTClm
select Rem.ClaimID, Rem.EID
from Rem, RemOut2
where Rem.CID= RemOut2.CIDand
Rem.EID= RemOut2.EIDand
(ReimProcessDate between @fromdate and @thrudate)
insert into #tmpWTClm
select distinct ClaimID, EID
from PaymentsDetail
where ProcessDate between @fromdate and @thrudate
insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimDenials
where DenialProcessDate between @fromdate and @thrudate
insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimAppeals
where AppealProcessDate between @fromdate and @thrudate
declare ClmDataCursor cursor for
select distinct ClaimID, EventID
from #tmpWTClm
open ClmDataCursor
fetch ClmDataCursor into @claimid, @EID
while (@@fetch_status = 0)
begin
insert into WTDenialAppealExtract
select distinct
from Rem, Rem1
where Rem.CID= Rem1.CIDand
Rem.EID= Rem1.EIDand
Rem.CID= @CIDand
Rem.EID= @eventid
fetch ClmDataCursor into @CID , @EID
end
close ClmDataCursor
Deallocate ClmDataCursor
Thanks
Hi,
id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.
What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined input parameters of the SP and generates the logging statement accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.
So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....
Any tipps would be appreciated...
cheers,
Stefan
Hi guys...
My goal is to change the given stored procedure so that I can find out the different age gorup according to users parameter and find out sumof these values for that group:
s.TVmins, s.Notional$, COUNT(*) AS Qty, SUM(s.TVmins) AS TVMinsAmt, SUM(s.Notional$) AS NotionalAmt
For that I am planning to put another parameter @count for the group interval and I need to group accordingly.
So my answer should look like:
if the user give the @count value as 10:
the result should:
age group TVMins Notional
1-9 1560 125632( the sum of that particluar group)
10-19 -- --
---
91-100 -- ---
I have a field DOB( Date of birth) , I have to extract age from that field first and then group them according to the parameter values and then find its corresponding sums...
<CODE>
-----------------------------------------------
ALTER PROCEDURE [dbo].[sp_PlanningData]
@ProgrammeID numeric,
@RegionID numeric,
@SiteID numeric,
@COCGroup varchar(50),
@Provider varchar(50),
@Schedule varchar(50),
@StartDate datetime,
@EndDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql = 'SELECT
dm.DOB,
dm.Suburb,
vs.RID,
s.TVmins,
s.Notional$,
COUNT(*) AS Qty,
SUM(s.TVmins) AS TVMinsAmt,
SUM(s.Notional$) AS NotionalAmt
FROM dbo.lkpService s
INNER JOIN dbo.tmpValidServices_ALL vs ON s.Code = vs.Service
INNER JOIN dbo.tmpDemographics_ALL dm ON dm.RID = vs.RID '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON vs.COC = c.pvcode '
IF @ProgrammeID IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '
SELECT @sql = @sql + 'WHERE s.Schedule = @Schedule '
IF @StartDate IS NOT NULL
SELECT @sql = @sql + ' AND (vs.Complete >= @StartDate ) '
IF @EndDate IS NOT NULL
SELECT @sql = @sql + ' AND (vs.Complete <= @EndDate ) '
IF @ProgrammeID IS NOT NULL
SELECT @sql = @sql + ' AND (ag.AgencyTypeID = @ProgrammeID)'
IF @SiteID IS NOT NULL
SELECT @sql = @sql + 'AND (ag.EXACT# = @SiteID) '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '
IF @Provider IS NOT NULL
SELECT @sql = @sql + 'AND (vs.Provider = @Provider) '
SELECT @sql = @sql + 'GROUP dm.Suburb,vs.RID, s.TVmins, s.Notional$ '
SELECT @paramlist =
' @ProgrammeID numeric,
@RegionID numeric,
@SiteID numeric,
@COCGroup varchar(50),
@Provider varchar(50),
@Schedule varchar(50),
@StartDate datetime,
@EndDate datetime '
EXEC sp_executesql @sql,@paramlist,@ProgrammeID,@RegionID,@SiteID,@COCGroup,@Provider,@Schedule,@StartDate,@EndDate
END
-------------------------------------------------------
</CODE>
Hope this will help.. it is really urgent one.. I am trying my best to find it out..
Thanks for your help..
To find parameter names
In ASP.Net 2.0, how can I get what parameters defined in an rdl report file? I know the report name and the report is deployed to a server.
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View 1 Replies View RelatedI want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).
View 6 Replies View RelatedI am trying to install SQL Server Express - (the non ADV version) and get the following 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_x64.msi'".
I have 64 bit Vista Ultimate running on an HP dv9000.
I am using the download from the bottom of the download page for SSX where it says 64 bit version.
Prior to this I got the. "64-bit ASP.net is registered. Required 32-bit ASP.NET to install Microsoft Reporting services 2005(32-bit)." messagd during the pre-install scan. From what I gather in the posts, the reporting issue is different from my installation package problem.
I have been trying to prove a point to friends about how far you can go with SSX but am having no luck installing it. This whole process took 1 hour in XP but I've been stymied for 2 days.
I would appreciate being pointed at the right downloads for what seems to be a simple install.
I can't find 'SQL Server: SSIS Pipeline' performance object in performance monitor on a 64-bit SQL Server. I see it on a 32-bit. Does anybody know why?
Thanks
Is there any way to find Report server database name using T-SQL?
I have SQL server report server instance and authentication credentials.
I can use sys.databases but in case of user had changed ReportDb name at the time of configuration this approach will not work then How I can find?
Hello!
We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).
The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.
During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:
Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)
Any help is greatly appreciated,
Lucio Gayosso
I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.
I have a code to find it in local DB....how can I modify to include all linked servers as well:
ALTER PROCEDURE [dbo].[FindTable]
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
[code]....
My ISP runs MS SQL server for me and does not have experience managing this type of database. For example, the database occassionally crashes and looses data. Our site is not live yet, but once we go live, this is unacceptable.
I need somebody to consult on the database configuration, tuning, security and maintenance plan. How would I find a person with the correct qualifications?
I have the following in my web.config<appSettings> <add key="strConn" value="server=10.100.1.2;uid=sa;pwd=;database=MyDB/></appSettings>and I am getting the following error message --"SQL Server cannot be found or access denied"if I replace the server IP address with server name, it works fine. Am I missing something here? Help!ThanksBugme
View 1 Replies View RelatedI installed MS SQL 2014 Standard. In the Data Tools in 2013 with the connection setup I do not see any server names. When calling sqlcmd -L all servers are visible. In SQL Configuration Manager Network Configuration I set the TCP / IP, Shared Memory and Named Pipes have set enabled. I installed all the server components.
View 6 Replies View RelatedHow to find total number of SQL Server which is running in localnetwork m/cs, through C# or from one of SQL Server? - Thanks in adv.
View 2 Replies View RelatedHello,
When I Installed SQL Server in my PC I didn't choose if I want to connect with Windows Authentication or SQL Server Authentication. So, I can't connect to neither of those, In the Connect to Server box I cannot find any server to connect. How do I connect to the server with the Windows Authentication ?
I need to deploy a multi-user application to several different customers. My app was built on VB.net and uses SQL Server Express. For remote client to connect to the server I understand that I need to use a connection string something like this:
€śServer=ServerName;Database=myDB;Trusted_Connection=True;€?
My question is, how will I know what the server name is? Can that be different for different customers? When I test this on my PC, I use (Server=.SQLEXPRESS;), but what do I use for deployment?
Thanks.
Hello;
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
Thanks
Hi,
I installed already vs 2005. The installation installed automaticaly SQL Server 2005 express. Now , I want to see which databases I have there and tables,views,store procedure etc. I tried the server explorer in the vs but I don't see any database on my pc. I tried also to open somthing like enterprise manager like I have in sql server 2000 but I don't found such a tool. I tried to find it in START=>Programs=>SQL Server 2005 but their I see only the Configuration tool. I want to know if the installation of the vs 2005 does not install Databases for the sql server Express ? Should I have to download this database samples? What about a tool to view those database like the enterprise manager in sql server 2000 ?
Thanks,
David
I need to find out how long my SQL Server has been running and I believe that there is a way of obtaining this info by querying a system table (or executing a procedure).
Does anyone know about this?
Karl
Hi if I do exec sp_MSforeachDB '......etc'
and i get SP sp_MSforeachDB not found how do i reference it in order for the server to find it.
Because when we check in master we find the SP there but when we run the exec sp_MSforeachDB ...
we get SP not found
any help pls
Hello.
I have a few reports that I've published to my reportserver.
Now, my rdl files have been delete from my local computer.
Is there a way to find the rdl files on the server so I can download them to my local pc so I can continue working on them?
I have full access to the report server of course (and the the sql server itself).
I'm useing rs2005.
Thanks.
I installed sql server on my PC, and when I look at the Programs I dofind sql server, but when I click on that I get "configuration tools".I don't see anything that says "enterprise manager" for instance. Howcan I find it so I can make a shortcut to it?Thanks
View 4 Replies View RelatedI am trying to get the CLR user-defined aggregate functions sample working.
I have made the DLL and I can create the assembly,
create assembly sqlutildll from 'D:SQLdatadllSQLutils.dll'
This went OK. But I can't create the function:
CREATE AGGREGATE Concat (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME sqlutildll.Concatenate
this is the error:
Msg 6556, Level 16, State 1, Line 1
CREATE AGGREGATE failed because it could not find type 'Concatenate' in assembly 'SQLutils'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
This looks like a simple error, but the name Concatenate is spelled correctly (it is a copy from books online see "Invoking CLR User-Defined Aggregate Functions" in BOL)
The error is "could not find type", but it is a class, might this be the problem?
Lost as a goose....
View 1 Replies View RelatedSay the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?
View 7 Replies View RelatedDear All,
I have installed SQL Server 2005 Express Edition, Iam not able to find SQL Agent, So anybody help me............................
Regards,
Vinayak
Hello,
My problem is this:
In the phase of installation of my software, how I can know if SQL Server 2005 (anyone edition) is installed in the guest system?
Thank you.