Database Engin Tuning Advisor, Not Helpful

May 27, 2007

Okay, so i'll just let you know right now, that i suck at making quries

i'm testing out a 2k5 eval copy of Sql Server (if i like, i'll migrate from 2k)
i have a db of almost 4 million names, with no indexes, and i'm trying to have
Sql tune a procedure that i wrote to see what it recommends for adjustments.
but for some reason it comes up with no results. any ideas why?

--Update
just noticed that if i type in the full query (not in the procedure form)
it recommends some indexes for me. is there any why to get recommendations
though the stored procedure below? i have about 112 quries that i want
to analyse, i could change them to actual quries if i had to, but it'd
take awhile

Current Table DDL:

USE [Test]
GO
/****** Object: Table [dbo].[mortgage] Script Date: 05/27/2007 01:52:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mortgage](
[fips_state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state_abbrv] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip_four] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[del_point] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_rte] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city_abbrv] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_house_num] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_pre_dir] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_st_name] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_st_suff] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_post_dir] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_unit_des] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_unit_desnum] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fips_cnty] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[census_tract] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[census_block] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lattitude] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[longitude] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fips_ispsa] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[wealth_rating] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[time_zone] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[homeowner] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[est_inc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_fname] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_mi] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_lname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_title] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_dob] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_ageconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_age] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_msconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_ms] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_fname] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_mi] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_lname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_title] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_dob] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_ageconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_age] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_pres] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_0_3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_0_3_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_4_6] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_4_6_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_7_9] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_7_9_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_10_12] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_10_12_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_13_18] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_13_18_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[religious_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[political_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[health_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[general_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_purprice] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_purdate] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_year_build] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[donate_env] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[char_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pres_cc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pres_perm_cc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_amnt] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_lender_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_rate] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_rate_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_loan_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dnc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_deed_date] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_amnt] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_lender_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_rate_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_month_term] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_loan_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Source] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[citystate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[countystate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[areacode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Stored Procedure:

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[search_orders_2] Script Date: 05/27/2007 01:53:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================


create PROCEDURE [dbo].[search_orders_2]

@lowagenvarchar(10)=null,
@highagenvarchar(10)=null,
@highwealthnvarchar(10)=null,
@lowwealthnvarchar(10)=null,
@highpurpricenvarchar(10)=null,
@lowpurpricenvarchar(10)=null,
@lowmtgamtnvarchar(10)=null,
@highmtgamtnvarchar(10)=null,
@lowincomevarchar(3)=null,
@highincomevarchar(3)=null,
@marriedstatvarchar(3)=null,
@lowyearbuildvarchar(4)=null,
@highyearbuildvarchar(4)=null,
@mtgpresstatvarchar(3)=null,
@lenderpresstatvarchar(3)=null,
@ratetsvarchar(3)=null,
@loantsvarchar(10)=null,
@childstatvarchar(3)=null,
@homeownerstatvarchar(10)=null,
@lowpurdatevarchar(8)=null,
@highpurdatevarchar(8)=null,
@addresstype varchar(3)= null,
@SPSirvarchar(3)= null,
@debttypevarchar(3)= null,
@dwelltypevarchar(3)= null,
@zipselectnvarchar(4000)=null,
@acselectnvarchar(4000)=null,
@stselectnvarchar(4000)=null,
@cityselectnvarchar(4000)=null,
@counselectnvarchar(4000)=null,
@tablenamenvarchar(2)=null,
@orderbynvarchar(2)=null,
@Zipcode char(11)=null,
@milesfloat=null,-- as
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000),
@highlatitude float,
@lowlatitudefloat,
@HighLongitudefloat,
@LowLongitude float,
@StartLatitudefloat,
@StartLongitude float,
@LatitudeRange float,
@LongitudeRange float

if @miles>0
SELECT @StartLatitude = lat, @StartLongitude = lng FROM zipinfo2 WHERE zip = @Zipcode
Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange
set @sql = ''
if @orderby is null set @orderby='1'
if @tablename is null set @tablename='1'
if @spsir='1'
select @sql = @sql+ 'SELECT '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
end +' o with (nolock) left join dbo.Spanish s on o.per1_lname = s.lname'
if @spsir='2'
select @sql = @sql+ 'SELECT distinct '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
End +' o with (nolock) inner join dbo.Spanish s on o.per1_lname = s.lname'
if @spsir='ALL' or @spsir is null
SELECT @sql = 'SELECT '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
End + ' o with (nolock)'
select @sql=@sql+' WHERE 1 = 1'
if @spsir='1'
select @sql = @sql+ ' and s.lname is null'

IF @lowage<>0 and @lowage is not null
SELECT @sql = @sql + ' AND o.per1_age >= @xlowage'

IF @highage<>0 and @highage IS NOT null
SELECT @sql = @sql + ' AND o.per1_age <= @xhighage'

IF @lowincome<>'DF' and @lowincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome'

IF @highincome<>'DF' and @highincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome'

IF @highwealth<>0 and @highwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth'

IF @lowwealth<>0 and @lowwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth'

IF @highpurprice<>0 and @highpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice'

IF @lowpurprice<>0 and @lowpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice'

IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild'

IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild'

IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'
IF @highmtgamt<>0 and @highmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt'

IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate'

IF @highpurdate<>'DF' and @highpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate'
IF @marriedstat in ('M','S')
SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat'
IF @ratets in ('V','F')
SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets'
IF @homeownerstat in ('H','R')
SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'
IF @childstat in ('Y','N')
SELECT @sql = @sql + ' AND o.child_pres = @xchildstat'
IF @lenderpresstat ='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>'' '''
IF @lenderpresstat ='N'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name='' '''

IF @mtgpresstat='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'
IF @mtgpresstat='N'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'
IF @loants<>'DF' and @loants IS NOT NULL
SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'
if @dwelltype='S'
SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''
if @dwelltype='M'
SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''
if @DebtType='1'
select @sql = @sql + ' and o.pres_cc=''y'''
if @DebtType='2'
select @sql = @sql + ' and o.pres_perm_cc=''y'''
if @addresstype in ('1','3')
select @sql = @sql + ' and o.address1<>'' '''
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <>
'DF' or @miles<>0
SELECT @sql = @sql + ' and (1=2'
IF @acselect<>'DF' and @acselect IS NOT NULL
SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )'

IF @stselect<>'DF' and @stselect IS NOT NULL
SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )'

IF @cityselect<>'DF' and @cityselect IS NOT NULL
SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'

IF @counselect<>'DF' and @counselect IS NOT NULL
SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'
IF @zipselect<>'DF' and @zipselect IS NOT NULL
SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'
if @miles>0
select @sql = @sql+ ' or o.zip in (SELECT z.zip FROM dbo.zipinfo2 z WHERE (z.Lat <= @HighLatitude
AND z.Lat >= @LowLatitude) AND (z.Lng <= @HighLongitude AND z.Lng >= @LowLongitude))'
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or
@zipselect <> 'DF' or @miles<>0
SELECT @sql = @sql + ')'
select @sql=@sql +case @orderby
when 1 then ' group by o.state_abbrv order by o.state_abbrv '
when 2 then ' group by o.state_abbrv,o.county_name order by o.state_abbrv,o.county_name '
when 3 then ' group by o.state_abbrv,o.city order by o.state_abbrv,o.city '
when 4 then ' group by o.zip order by o.zip '
end
IF @debug = 1
PRINT @sql

SELECT @paramlist = '@xlowageint,
@xhighageint,
@xlowincomenvarchar(4000),
@xhighincomenvarchar(4000),
@xmarriedstatnvarchar(4000),
@xhighwealthint,
@xlowwealthint,
@xhighpurpriceint,
@xlowpurpriceint,
@xlowyearbuildnvarchar(4000),
@xhighyearbuildnvarchar(4000),
@xlowmtgamtint,
@xhighmtgamtint,
@xmtgpresstatnvarchar(4000),
@xlenderpresstatnvarchar(4000),
@xratetsnvarchar(4000),
@xloantsnvarchar(4000),
@xchildstatnvarchar(4000),
@xhomeownerstatnvarchar(4000),
@xlowpurdatenvarchar(4000),
@xhighpurdatenvarchar(4000),
@xzipselectnvarchar(4000),
@xacselectnvarchar(4000),
@xstselectnvarchar(4000),
@xcityselectnvarchar(4000),
@xcounselectnvarchar(4000),
@highlatitude float,
@lowlatitudefloat,
@HighLongitudefloat,
@LowLongitude float,
@StartLatitudefloat,
@StartLongitude float,
@LatitudeRange float,
@LongitudeRange float'

EXEC sp_executesql @sql, @paramlist,
@lowage,
@highage,
@lowincome,
@highincome,
@marriedstat,
@highwealth,
@lowwealth,
@highpurprice,
@lowpurprice,
@lowyearbuild,
@highyearbuild,
@lowmtgamt,
@highmtgamt,
@mtgpresstat,
@lenderpresstat,
@ratets,
@loants,
@childstat,
@homeownerstat,
@lowpurdate,
@highpurdate,
@zipselect,
@acselect,
@stselect,
@cityselect,
@counselect,
@highlatitude,
@lowlatitude ,
@HighLongitude,
@LowLongitude,
@StartLatitude,
@StartLongitude,
@LatitudeRange,
@LongitudeRange



Query to analyse:

Exec Search_orders_2
@mtgpresstat = 'Y',
@homeownerstat = 'H',
@stselect = 'PA,RI,SC',
@addresstype = '2',
@spsir = 'ALL',
@debttype = 'ALL',
@dwelltype = 'ALL',
@tablename = '1',
@orderby = '1';

View 20 Replies


ADVERTISEMENT

Regarding Database Tuning Advisor

Mar 27, 2007

Hi guys, I am new to use Database Tuning Advisor. Right now I just save a workload as a sql and runs it in Database Tuning Advisor. The script that I wrote is kinda simple ( Select EID,EName,EDescription From Events Where EID = 300) . However, after I finish analyzed it, there's nothing to display on the recommendations session. Is it suppose got any details in it ? Or does it mean I no need to tune my query performance ? Hope someone can help me out here. Thx guys.



Best Regards,

Hans

View 1 Replies View Related

Database Engine Tuning Advisor

Sep 7, 2007

Hi experts! I would like to ask for some help regarding Database Engine Tuning Advisor. I was trying to create Session Monitor then I choose TABLE as a workload. Then after creating and selecting the corresponding setup then I start the analysis, during the analysis it prompted an error?

Error MSG:
The specified workload(file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table.

But if I tried to use the Workload FILE instead of the table the session is successful and completed the analysis. My SQL current setup is client only, I was just accessing the server. Pls help me how to fix or do I need to configure something? Badly needed your help experts. Thanks in advance.


Tatas move

View 3 Replies View Related

The Best Way To Use The Database Engine Tuning Advisor

Oct 1, 2007

Hello all,I want to use the SQL Server 2005 Tuning Advisor on our database, andI'm hoping someone here can just confirm the steps for me. We have a10GB database that has a number of applications hitting it constantly,all performing many SELECTs, INSERTs and UPDATEs. Are the followingthe steps I need to take?1. Stop all the applications hitting the database.2. Make a backup of the database.3. Start the profiler on the original database.4. Turn on all the applications again.5. Wait for a time before turning off the profiler and saving theprofile.6. Run the tuning wizard against the backup database using the savedprofile.Do I have to do this every time or am I missing something obvious? I'munder the impression I need the backup because our database has a highvolume of INSERTs and UPDATEs so I have to make sure the state of thedatabase matches the state of the profile.As a matter of interest, why is it not possible for the tuning wizardto analyse the database using the *current* activity? Why does it haveto be a 'pre-recorded' workload?Many thanks,Tommy.

View 4 Replies View Related

Database Engine Tuning Advisor Does Not Support SQ

Jan 23, 2008

Hi to all,

I am using SQL 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error messsage like this


Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)

Help me to slove my problem.
Thanks in advance.

Regards,
Jose.P

View 1 Replies View Related

SQL2K5 Database Engine Tuning Advisor

Jan 24, 2006

Hi,I find much regrettable that Database Engine Tuning Advisor be not part ofMS-Express Edition ... A server without such help is not a server.Besides, you've got the tutorials, but not the tool to play with :-(((Regards,JM Blaise

View 1 Replies View Related

Database Engine Tuning Advisor Configuration?

Nov 29, 2007

I was trying to get some indexing recommendations from the Database Engine Tuning Advisor by consuming a trace file with known indexing issues. After several minutes all it did was create a report or two which list existing indexes. Not very useful. I must be doing something wrong.

A message displays that "75% of the consumed workload has syntax errors." I guess it can't take a regular trace (.trc) file?

I am conderned that the trace file I used did not contain what the Advisor needs. Do I need to setup the trace a special way to support the Advisor?

Also, what does "Database for workload analysis" mean? I chose tempdb but there is no Help, and BOL is a maze of small uninformative snippets . . .

Thanks,

Michael

View 3 Replies View Related

Database Engine Tuning Advisor Error

Jul 3, 2006

I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !

It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?

Joanna

View 1 Replies View Related

Database Engine Tuning Advisor Does Not Support SQL Express.

Jan 23, 2008

Hi to all,

I am using SQl 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error message like this..

Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)

Help me to solve my problem. Thanks in advance.

Regards,
Jose.P

View 7 Replies View Related

Provider Error On Using Database Tuning Advisor Remotely

Dec 5, 2007

Hi,

I am experiencing a Named Pipes Provider error 40 message as detailed below. I get this when I'm running the dta utility on a production server which is trying to remote onto a test server to use the metadata and optimizer there.


The error reported is...

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


(note: there is no (Microsoft SQL Server, Error: xxx) reported at the end of the error string)


The error results from the following cmdline syntax run from the production server.

dta -A 0 -E -e C:TuningLog.xml -F -ix C:DTA.xml -N OFF -of C:outputdta.sql -rl ALL -s DTA_20071205


Prod box: 64bit SQL2005 SP2 Std Ed Named instance, TCPIP and Named Pipes enabled, SQL Browser service running.
Test box: 32bit SQL2005 SP2 Std Ed Default instance, Listening for local and remote connections on TCPIP and Named Pipes (configured through the SAC), SQL Browser service running. From the SQL Log on the test box I can see TCPIP is listening & Named pipes is listening on \.pipesqlquery


There is no firewall configured on either box.

I can connect to the test server from the prod box using sqlcmd -E -S<servername>
I can ping the test server from the prod box


Would you have any idea what I'm doing incorrectly? The genericity of the error message doesn't give me much of a clue and the resolutions I've read all revolve around SQLBrowser, surface area config, and SQL Express which I've covered off.


many thanks

Matthew Eames

View 1 Replies View Related

Database Engine Tuning Advisor - E000 SqlServer Errors

Nov 22, 2007

Does anyone know how to DTA to correctly read the output from the Profiler?

I get the error:

TITLE: DTAEngine
------------------------------
50% of consumed workload has syntax errors. Check tuning log for more information.
------------------------------
BUTTONS:
OK
------------------------------


And the log is full of lines like:

E000 SELECT COUNT(*) FROM Pictures WHERE AdRecId = 16329 2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'Pictures'.

E000 exec GetAd @RecId=15282 8 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'GetAd'.

E000 exec GetAd @RecId=15385 4 [Microsoft][SQL Native Client][SQL Server]SHOWPLAN permission denied in database 'xxx'.



Does anyone know what causes this?

I do have showplan permission on the login I use for tuning.

View 7 Replies View Related

Database Engine Tuning Advisor Gives Non Existent Errors In SQL 2005

Aug 14, 2006

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:



E000 exec dbo.cs_user_Get @UserName=N'jded',@UserID=0,@IsOnline=0,@LastAction=N'',@SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @ThreadID=5969,@UserID=28236,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=414,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @SettingsID=1000,@PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.





The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.


Any help will be greatly appreciated.

Thank you.


View 7 Replies View Related

Data Tuning Advisor

Apr 20, 2006

Is Data Tuning Advisor available for any Express version?

--Thanks

View 2 Replies View Related

A Few Questions On The Tuning Advisor

Apr 2, 2007

i decided to give the tuning advisor a go, just as I had some spare time. I ran profiler against a database for about a half hour, then used the resulting trace file as the input for the tuining advisor.. A lot of the sprocs called in the trace file cannot be executed again,as they will cause issues with data integrity and if someone tries to execute them , an error is produced detailing primary key violations. the error tuning advisor actually shows is this:



"51% of consumed workload had syntax errors. Check tuning log for more information".



My question is though:



1. For tuning advisor to be effective, does the code/trace its analysing have to be valid, or will the now invalid data in my trace file work?



2. Does the tuining advisor actually run the sql against the database, effectively performing changes to data, or just run an execution plan of the query?



3. is the error i recieve expected or something im doing wrong?


Thanks all for any answers.

View 3 Replies View Related

Auto Tuning Advisor

Nov 29, 2007

Hi.

We are using the possibility to analyse the output of profiler with tuning advisor and apply recommendations.
Is it possible to do it automatically?
For example: While a new application and its database has been released and the staging cycle the profiler runs all time and save the output automatically to an xml file and at the end of a month (run as a job) the tuning advisor gets the file, analyse it and apply the recommendations.

Recapitulatory, I need to know how to realize:
- auto save the profiler output
- auto analyse the profiler output and apply by tuning advisor

Thanks a lot.
Anatoli Rejditsch.

View 3 Replies View Related

Data Tuning Advisor

Apr 20, 2006

Is Data Tuning Advisor available for any Express version?

--Thanks

View 1 Replies View Related

Tuning Advisor: &"Could Not Find Stored Procedure&"

Dec 13, 2007

I'm running Database Engine Tuning Advisor over a huge workload table, and getting many errors like the following:

quote:E000
declare @p19 int
set @p19=228
exec MySProc @Cod='T12',@Nro=default,@IdM=727,@Rec=@p19 output
select @p19

1
[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'MySProc'.

MySProc exists in the database to tune, and I'm not sure if every record that references that SP fails or it is random, because once it also logged S001 messages for the declare, set and select lines of the same call with other parameters.

What's going on?

BTW, I uploaded my trace (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93801) to another 2K5 server where I also restored a backup of the traced database taken a couple of days later. It's strange that DETA requires that the database to tune and the database where the workload table resides must be on the same server!!!

Oh, DETA is still running after 23 hours of analisys

Thanks in advance.

++Vitoco

View 3 Replies View Related

SQL 2012 :: Recovery Advisor Won't Open Or Start For Specific Database

Jul 1, 2014

Today we were trying to use restore wizard (recovery advisor) to restore a database which is over 80 GB, but the wizard won't load or start. However the wizard dialog box opens without any problems with all other databases stored in the instance. Is there any problem or reason to why the restore wizard won't come up?

This is SQL 2012 enterprise version we are running.

View 1 Replies View Related

Was This Post Helpful

Mar 20, 2007

If u get any solution then plz share knowledge with me because I am new in SQL

View 3 Replies View Related

Query Tuning And Stored Procedure Tuning

Nov 22, 2000

Hi

Is there any good books for Query Tuning and Stored procedure Tuning

Thanks

View 1 Replies View Related

Helpful Blogs - Take A Look Before Posting

Aug 29, 2007

I think we could solve quite a few questions much faster by posting helpful blogs here.
Some questions are asked over and over and the answer is typically: see this blog. People simply don't know it's out there.

So, if you are the author of a useful Reporting Services blog, or have found a Reporting Services blog particularly helpful, please post it here (keep it strictly to links that you feel solve a large number of RS questions).

If you would, also provide a short description as to the content of the blog.


I'll start with these:

Session Timeouts -- http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
SSRS Setup -- http://prologika.com/CS/blogs/blog/archive/2007/07/12/ssrs-setup-woes.aspx
Complex SQL Issues -- http://spacefold.com/lisa/category/SQL-Server.aspx

View 2 Replies View Related

DBCC Checkdb Might Soon Be Very Very Helpful To Me. MAYBE Hardware Problems

Nov 12, 2006

Hey guys. I've a question for you. I don't know where to start for the question but we have a SAN with 2 nodes and an activeactive cluster built on it with SQL 2005 ent edition installed. I've started running DBCC checkdb(db1,noindex) recently and am doing it every week. My question is this. I DON'T TRUST MY HARDWARE. I very often used to get the message that

'The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000b75e74000 in file 't:MSSQLDatafile1Index.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.'

I also get the error described in http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx. I just finished running dbcc checkdb on my db today and luckily have come up with no errors.

I'm wondering what are the steps I should take to convince my higher ups that there are problems with Hardware and we are eventually going to have problems if something is not done about the hardware. I'e been taking diff and tran and full backups on this db but i don't want to go that route if I can get them convinced that it's not SQL. We have been on SQL 2005 for about 1 month now. This problems were there since 2000. Now, they are less recurring. Since, I've been on 2005, today is the first time I ran reorg
eindex for my db. Everything was fine. But, WHY ARE THESE I/O ERRORS/INFO OCCURING? All your help is appreciated. Thank you.

View 8 Replies View Related

Index Tuning With Empty Database

May 15, 2000

HI

I have this complex logical model with 60 tables which i have converted to phycical model and into sql 7.0 database now the database is completely new for a new internet project without even single row of data now i have to optimise database and determine if the indexes created by the the designing team are good in sql 7.0 will the give me optimised database or not . I just dont know what to do


1) is it possible for me to use indextuning wizard without the data
whenever i try to to use indextuning wizard it askes me to use a workload file or make workloas file useing a trace as i do not have the data i just have to capture a trace by saying select * from <table>
as the work load file does not return any records the index tuning wizard does not suggest any changes In the indexing
tell me HOW DO I DETERMINE WHAT INDEXES TO CREATE FOR A OPTIMISED DATABESE ?


2) IT IS NOT POSSIBLE FOR ME TO PUT DATA IN ANY ONE TABLE AS ALL THE TABLES HAVE fk AND pk RELATION SHIP so what do i do


3) WHAT DOES dba normally do when he recives an empty data base to optimise

4) where do i make views to optimise the data


5) how do I populate data in so many tables

will puting dummy data help ?


Can some one help me with some SP which will populate the table with dummy data by some kind of do while loop with counter incrementing with 1000 rows

please help me
and if i am not clear
pl tell ask me the questions on my default email or

johnusa44@hotmail.com


john or G2

View 3 Replies View Related

Database Engine Tuning Recommendations

Feb 11, 2008

Hi,

I'm just testing the database engine tuning advisor.

I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this.

The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.

Is it normal for DET to recommend that any index not referenced by the workload file be dropped?

View 3 Replies View Related

Getting Started With Database Tuning Adviser

Oct 11, 2007



I generated several workload files over a two day period against my production server. When I run the tuning adviser can I run it against my development database or should I run it against the production database?

Thanks.

View 3 Replies View Related

Nightly Job Has Been Running For Months. Now It Crashes Without Any Helpful Error Message.

Apr 11, 2007

2:30:03 AM SQL Server Scheduled Job 'Our Job Name' (0x888B36086CCCC34FB353B408DEBE0432) - Status: Failed - Invoked on: 2007-04-11 02:30:00 - Message: The job failed. The Job was invoked by Schedule 30 (Our Job Name). The last step to run was step 1 (Our Job Name).



Where do you begin to discover the cause of the crash?

View 6 Replies View Related

Data Flow Job Failing, Destination = Microsoft SQL Native Client, Error Message Not Too Helpful...

Dec 14, 2007

Hi there,

I have a Data Flow task which uses an XML File Source with six parellell Outputs, each going firstly to a Data Conversion Task, then the results of each end in a SQL Server Destination Object. (All using the SQL Native Client)

To eplain this further, the Xml file contains 6 different types of elements, the Dataflow splits out each type of element and processes them into different tables. The Data Transformation object exists only because the XML fields are Uni-code and the table fields are VarChar not nVarChar.


Initially using this setup I found that the Connection would timeout using the SQL Native Client so I changed the Timout on the Destination Objects to 0. This fixed the problem to some degree, however at present I can run the Pakage using the Visual Studio enviroment and everything works fine, no problem. When I run the Dtsx file using the SQL Server Agent, I end up getting the error below...



Error: 2007-12-14 14:33:19.16 Code: 0xC0202009 Source: Import XML File to SQL SQL - CP [2746] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from

I understand that this error is somewhat of a 'catch all' and that the way the Native SQL Server Connection object works makes Error Capturing difficult. I have tried a few things which I will list as I'm sure they will be suggested...


I have played around with the 'MaxInsertCommitSize' property of the SQL Server Destination Objects to no avail (IE, changing to 50000, 10000, 1000 all of which resulted in the same problem)

I am running the ssis pakage from the server which is the destination

As mentioned above the Timeout on the SQL Server Destination Objects is set to 0

What I have already mentioned and still don'tt quite understand is that I can run the job successfully from the Visual Studio enviroment but as a job run off the SQL Server it fails...



Can

View 8 Replies View Related

Not Running Advisor

Nov 18, 2007

when i run upgrade advisor at that time i got the error msg:

SQL Server version:09.00.3054 is not supported by this release of Upgrade Advisor.
can you tell me how to slove this problem

Thanks

View 1 Replies View Related

Error On Upgrade Advisor

Jun 8, 2006

Hi there,

I tried to run Upgrade Advisor on two SQL 2000 servers, but the wizard keeps failing when it reaches 22% while analyzing the Database Engine.

I downloaded the latest version, but still receive the same error...

Error: "SQL BPA command line has encountered a problem and needs to close"

Both servers are Windows 2003 Enterprise Edition SP1 with SQL 2000 Enterprise Edition SP4.

Does anyone have an idea what I can do to fix this problem.

Thanks!!

View 38 Replies View Related

Upgrade Advisor Issue

Oct 12, 2007

Hi,

I have a SQL server 2000 in development. it is running as an instance (servernameinstancename) with a TCP port configured. I can not connect to this server thru upgrade advisor wizard as well as by using command line utility. Can some help me understand how can I connect to sql server 2000 name instance and run upgrade advisor on it.

Here are the steps I have taken:

created config.xml as below:


<Configuration>
<Server>servername</Server>
<Instance>D15</Instance>
<Components>
<SQLServer>
<Databases>
<Database>Testdb</Database>
</Databases>
</SQLServer>
</Components>
</Configuration>
It does not allow me to put port number and can not connect to server.

I get following error:

Unable to connect to server. Reason: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

But it is not a sql server 2005 system and I can connect to this server via SSMS or SQL EM.


Thanks
Rgupta

View 2 Replies View Related

Can't Connect With Upgrade Advisor

Sep 13, 2006

I'm having trouble connecting the Upgrade Advisor to a SQL 2000 box at a client. Here's the setup:

1> I'm running UA on a Virtual PC image

2> I can run UA against other SQL machines on the same domain

3> I can register and administer the SQL server I'm having trouble with in Enterprise Manager

4> I have SA rights on the SQL box I'm having trouble with.

5> The problem is when entering the server name and clicking "detect," it errors out with "The network path not found (mscorlib)."

6> I've tried server name, FQDN and IP address.

This is a fairly convoluted layout, so there could be a firewall issue that I'm not aware of (but I don't think so).

Any help would be appreciated!

Bob

View 2 Replies View Related

SS2005 Upgrade Advisor

May 29, 2008

I'm trying to migrate/upgrade some databases from 2000 to 2005 and am having a problem. Apparently, the Upgrade Advisor can't analyze a SS2000 database if it is in a named instance. (see below)


This problem occurs because the SQL Server 2005 Upgrade Advisor cannot connect to the named instance of SQL Server 2000.

The SQL Server 2005 Upgrade Advisor uses information that the SQL Server Browser service returns when the SQL Server 2005 Upgrade Advisor tries to connect to an instance of SQL Server 2000. However, the SQL Server Browser service cannot return the correct information about the connection request. Therefore, the connection fails.

http://support.microsoft.com/kb/908454

Doesn't this make the UA tool useless for named instances on SS2000? Are there any plans to correct this issue? Or, is there a workaround available?

Keith

View 7 Replies View Related

Upgrade Advisor Report

May 20, 2008

I ran Upgrade Advisor on a SQL 2000 server and I received warnings and errors for DTS packages and MetaData Services. Since there are no DTS packages and I don't see anything when I click on Meta Data Services Package, but I get an error when I click on Meta Data...

"An error occurred while trying to access the database information. The msdb database could not be opened."

Why is this error occurring when there are no DTS packages?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved