Hello, friends!
sorry for the stupid question:
I created a new index on table and I'm looking to a command equal to "Analyze table ", "Compute statistics "in Oracle
to check if that index is usefull.
I will be grateful if you could answer a few more questions around Analyzing Key Influencers
1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a €˜Score€™ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?
2. Can I access this €˜Score€™ and recommended inputs above programmatically?
3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?
5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the €˜Score€™? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?
6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?
Excuse the elementary question; I am new to this feature.
No matter what dataset I use, I get the following error: "The task was not able to detect any key influencers for the 'xxx' column. The values of 'xxx' seem unrelated to values of other columns."
Dear All, i've used the DBCC showcontig command against my table table103 but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....
Anybody nows a tool to analyze LDF files in MS SQL Server 2000?I mean, a tool that converts a LDF file in a set of SQL transactions?(similar to dbtran in sybase)thanks!
Hi, the other day, some data was deleted by mistake, the data that we wanted to delete was in just 1 table, and we deleted the related data in a couple tables more...
We do full backups every Sunday and a Differential every day, my question is:
Is there any way to analyze the backup file to compare the backed up data with the data that the table has now, and by automatic means restore just some rows to the table, or at least see the data to insert it manually?
Msg 245, Level 16, State 1, Procedure CompactL1RecordsFromfirstINTRAD, Line 177 Conversion failed when converting the varchar value 'DECLARE rows_cursor CURSOR FOR SELECT ask, dateTimed FROM iDay_Compr_GOOG WHERE DAY (dateTimed) = ' to data type int.
I get this message while executing a stored procedure. It works "half way" but skips this statement and the whole block as a result. In the table @table_name ask is declared as float and dateTimed as DateTime. Similar statements work happily in other stored procedures with no problem. Even in the same stored procedure I have places where I use DAY (dateTimed) and they seem to work as far as I can see although it is a very branched out code with many IF ... ELSE's.
SET @SQL = 'DECLARE rows_cursor CURSOR FOR SELECT ask, dateTimed FROM '+@table_name+' WHERE DAY (dateTimed) = '+@day+' AND NOT ask = 0' exec sp_sqlexec @SQL OPEN rows_cursor FETCH LAST FROM rows_cursor INTO @askQ, @lastTableDateTime SET @SQL = 'UPDATE '+@table_name+' SET askSize = askSize + '+@askSize+' WHERE dateTimed = '+@lastTableDateTime+' AND ask = '+@askQ exec sp_sqlexec @SQL -- <== this is line 177 CLOSE rows_cursor DEALLOCATE rows_cursor
Another puzzle for me is the line number. I used Edit-->GO TO--> 177 to single the line out and it seems to point to a different exec sp_execsql @SQL statement, the one that is down the road.
I can make neither head nor tail out of it. I am sure Jens, Cetin or Andrea or whoever stumble on this post will be able to figure this out. Anyone else's help will also be appreciated.
I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.
When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?
I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?
I have been through "Getting Started" and set the AS connection to the local AS Server.
Edit 2007-8-9: Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the -- output from the sp_spaceused stored procedure -- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select [FileSizeMB]= convert(numeric(10,2),sum(round(a.size/128.,2))), [UsedSpaceMB]= convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) , [UnusedSpaceMB]= convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) , [Type] = case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end, [DBFileName]= isnull(a.name,'*** Total for all files ***') from sysfiles a group by groupid, a.name with rollup having a.groupid is null or a.name is not null order by case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end, a.groupid, case when a.name is null then 99 else 0 end, a.name
create table #TABLE_SPACE_WORK ( TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE_USED ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME SYSNAME not null , TABLE_ROWS int not null , RESERVED int not null , DATA int not null , INDEX_SIZE int not null , UNUSED int not null , USED_MBnumeric(18,4)not null, USED_GBnumeric(18,4)not null, AVERAGE_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null, )
declare Cur_Cursor cursor local for select TABLE_NAME= rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE' order by 1
) select TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED, USED_MB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024),4), USED_GB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024*1024),4), AVERAGE_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),RESERVED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_DATA_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),DATA))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_INDEX_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_UNUSED_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),UNUSED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end from ( select TABLE_NAME, TABLE_ROWS, RESERVED= convert(int,rtrim(replace(RESERVED,'KB',''))), DATA= convert(int,rtrim(replace(DATA,'KB',''))), INDEX_SIZE= convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), UNUSED= convert(int,rtrim(replace(UNUSED,'KB',''))) from #TABLE_SPACE_USED aa ) a order by TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc go
drop table #TABLE_SPACE_WORK drop table #TABLE_SPACE_USED drop table #TABLE_SPACE
Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
I want to analyze procedure cache, to find inefficient plans and parameter issues.
I do it trow DMV But my requests to DMV are very slow and demand resources because procedure cache is about several GB Actually I dont need on-line analysis.
Is it possible to have fast snapshot of procedure cache?
I have installed the excel DM addin and am trying to work through the tutorials -
When I run the 'Analyze Key Influencers' tool against the sample data through a remote AS server I get: The task was not able to detect any key influencers for the 'Purchased Bike' column. The values of 'Purchased Bike' seem unrelated to values of other columns.
however when I run it against a local AS server I get the expected results.
I can see no differences in settings or setup between the AS instances I am trying to use - perhaps a permissions issue? Thank you
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?Â
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this: CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey OR LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?