Dataservererror

465IndexAACID,397Analyzingpeakloaddata,446applicationusage,446anomalyviews,447capacityplanning,446observations,447performance,446SLA,448thirdpartymonitoring,446transactionmonitor,448virtualIP,446weekendanalysis,449conguration,455assesment,456capturesettings,456databaseproperties,457sp_congure,456validation,456virtuallogles,457perfmon,449disksec/read,452hyperlinktosupport,451lineview,451,453logicaldiskcounters,452performanceanalysisoflogs,450reiewinglineview,454report,performanceanalysisoflogs,450reportview,452reportviewreview,454review,449timeslices,450withPALreview,453perfmonmaxservermemory,450SQL,458analsisbehaviour,459clusteredkey,461currentactivity,script,459extraction,transformationandload(ETL),460identifyingtotune,461indexes,460I/Operformance,458observations,459reportingdatabases,460traces,458transactionaldatabases,460uniqueblockingchains,460usagestatistics,460ApacheSqoop,409AssociationforComputingMachinery(ACM,20Atomicity,consistency,isolation,durability(ACID),397Auditing,221applicationlogs,232creatingT-SQL,226databaseauditspecication,230dene,226eventalerts,232databasebackup,233databasecreation,233errornumber,alert,233newpage,234Windowsapplicationlog,232fn_get_audit_le,231inSQLserver,224ltering,225logfailures,225rolloverles,225serverlevel,225userdened,225INDEX466Auditing(cont.
)ttquery,231serveraudit,226Corp_Audit,229T-SQL,226serverauditlecreation,227specications,serveraudit,228Automatingadministration,235tasks,263backupsandrestores,267databaseintegrity,269indexmaintanence,269monitoring,264statisticsmaintanence,270tools,235dynamicmanagementviews(DMVs),237monitoringperformance,235PowerShell,262SQLserveragent,238BBackupsandrestores,375automatingongroups,386logbackups,386T-SQLscripts,388availabilitygroupsetting,376SQLserveravailablility,377enablingbackups,383location,383performing,383priority,384removingreplicas,384failoverclusteringservice,376recoveryongroups,391restoreddata,392Windowsserver,congure,376Boyce-Coddnormalform,23Businessbubble,indexing,191dataintegrity,193description,193foreignkeyindex,195foreignkeyquery,194unindexedforeignkeysquery,193description,191indexbusinessusage,191CChecklists,136dailychecklists,136automate,136backups,136integrity,137security,137SQLserverjobs,137longertermitems,137audits,137patches,137SQLserver,138Cloud-computing,293ClusteredKey,461CommonLanguageRuntime(CLR),144CommunityTechnologyPreview(CTP),397Compliance,221healthinsuranceportabilityandaccountabilityact(HIPAA),223data,223healthandhumanservices(HSS),223protectedhealthinformation(PHI),223transactions,223Sarbanes-Oxley(SOX),221committeeofsponsoringorganizations(COSO),222databaseactions,223informationsystemsauditandcontrolassociation(ISACA),222internalcontrols,222regulateddata,223reports,222securitiesandexchangecommision(SOC),222Compression,335database,whatandhow,337creationofobject,337objectsbypartition,337rebuildaheap,337editionsandsupport,336fragmentandlogCOMMITTRAN,357createDB,356LOP_COMPRESSION_INFO,358transactionlog,356page,341applying,DBCCIND,343applyingdictionarycompression,345columnprex,applying,344dataondisk,345dictionarycompression,342structure,reaarange,342row,338compressedrecord,341createandinsert,datarecords,338467INDEXdatarecords,338DBCCIND,339internaldatarecords,340transactionlevels,SQLserver,340workingwith,346allocationunits,347datatypes,allocation,348fragmentandlog,355IN_ROW_DATA,348LOB_DATA,350patternusage,354scanandupdateDB,352sp_estimate_data_compression_savings,355sp_MSforeachtable,347congurationmanagementdatabase(CMDB),433Continuationofoperationsplanning/Disasterrecovery(COOP/DR),335ControlObjectivesforInformationandRelatedTechnologies(COBIT),222Ddatabaseadministrator(DBA),429Databasearchitectureinfrastructure,294authorization,294DoSattacks,294instances,294replicationtopology,295security,294instanceavailabilityandfailover,295physicalservers,295DatabaseConsoleCommandDs(DBCCs),315Databasedesignforperformance,17requirements,18implementationnon-specic,19waterfallmethod,18tablestructure,20history,20normaldatabasevs.
extraordinaryone,21physicalmodels,33relationaldatabasemanagementsystems(RDBMS),21relationalmodel,20testingthedesign,40DatabaseEngineTuningAdvisor(DTA),170Databaseutility,135checklists,136automation,136dailychecklists,136longertermitems,137datacollection,143AlwaysOnfeature,156businessindicatorkeymanagement,156commonlanguageruntime(CLR,144dynamicmanagementviews(DMV),144errorlogs,148extendedstoredprocedure,143failedjobs,152fragmentingindex,149indexes,149mirroringdatabase,154performanceofstoredprocedure,151processor,146sp_Cycle_Errorlog,149SQLserverreportingservices(SSRS),153storage,144sys.
dm_exec_query_stats,151systemtables,143layouts,138creatingutility,140datastorage,138referentialintegrity,140schemas,140tablestructure,141usingdata,158automating,datacollection,158errorhandlingandarchives,158jobtask,159schedulingdatacollection,159Datacapture,peakload,436applicationperformance,444applicationproling,444guidelines,444prolerandattributes,445transactionalactivity,444datacollectormanual,437monitoringapplications,439objectcounters,439performancecounter,438setcreation,437stopcondition,443datcollectorscheduling,443performancegathering,436counter,438datacollector,436dependency,436metrics,436monitoring,439INDEX468Datacapture,performancegathering(cont.
)ttobjectcounters,439perfmon,436principles,436scheduling,443stopcondition,443DataDenitionLanguage(DDL),275Datareferentialintegrity(DRI),140data-tierapplication(DAC),310Datawarehouse(DW),362DedicatedAdministratorConnection(DAC),13DefaultTrace,108DenialofService(DoS),294Devise,plan,462technologies,462tuneplan,462workloadoptions,462Distributedpartitionview(DPV),333DynamicManagementFunctions(DMFs),71Dynamicmanagementobjects(DMOs),163environmentbubblecolumnuniqueness,166equalitycolumnsscenario,166inequalitycolumnsscenario,167missingindexinformation,163missingindexquerycolumns,164performanceimpact,163recommendations,165Dynamicmanagementviews,71physicalperformancequeries,87databaseenginetuningadvisor,89dm_db_missing_index_details,88dm_db_missing_index_groups,88dm_db_missing_index_groups_stats,88index,partitioned,92missingindexes,87partitionstatistics,92rowpartitions,93tables,91tablescansvs.
indexscans,90tuninggains,90unusedindex,91processbasics,71joiningprocesses,74naming,72relatedviews,72tuningDBs,74varbinaryhashvalues,73variablelength,73systemtuning,93activequeries,94OSprocessesonDBserver,96performance,93readtables,98requestavailability,94sessionsandpercentage,93threadIDmatching,95transactionalsnapshot,97virtualtable,98waittypes,95tuning,74accuracy,79avgtimevs.
no.
ofexecutions,86blockingsession,77challengingqueries,81connectedsessions,75counts,execution,81,86currentrequests,77demo,currentrequests,76dm_exec_connections,74dm_exec_query_plan,79dm_exec_query_stats,80dm_exec_requests,75dm_exec_sql_text,79executingrequests,75inspection,85lockescalation,77lockingescalationdemo,78performanceofCPU,85performanceoptimization,82performancescripts,80physicalreads,87query,SQLstatements,83readdata,87reportsofperformance,84retrieveconnections,74SQLperformance,78terminologydivergence,82XMLplans,82Dynamicmanagementviews(DMV,439Dynamicmanagementviews(DMV),237DynamicManagementViews(DMV),324Dynamicmanagementviews(DMVs),1DynamicManagementViews(DMVs),144,304EEnvironmentbubble,indexing,162analysisperformance,188DTAanalysisreportlist,190XMLinputleconguration,188databasepreparation,186469INDEXcloning,186PowerShell,186ServerManagementObjects(SMO),186description,162missingindexesidentication,162attributes,162DMOs,163plancache,168recommendation,162sampleworkload,184workloadcollection,171extendedevents,175SQLTracesession,171FFailoverclusterinstance(FCI),282Federations,SQLdatabase,300datatypes,303features,301limitations,303rootdatabase,300scenario,302ALTERFEDERATED,302creatingfederation,303FEDERATEDON,302split,303schemachanges,304T-SQLchanges,301commandsandviews,302statements,301userinterface,304Firewalls,298Fluiddynamics.
SeeSQLserverdatamovementGGloballyuniqueidentier(GUID),278Gotchas.
SeeHiddenperformancesHHadoopimplementation,396Hashing,298Hiddenperformances,43predicates,43b-tree,46cases,46causes,49clusteredindex,58conversion,56datatypes,52dene,43evaluatingfunctions,55executingindexseek,47executingkeylookup,50executionplans,44function,54implicitconversion,57indexarchitecture,46inefcientcluster,59keylookup,51no,57nvarchar,57optimalindexes,45OrderDate,47OrderStatus,47scan,index,48secondarylter,48subtotalindex,50usageofindex,51residuals,59actualjoins,64buildandprobe,63hashjoins,62hash,without,64joinoperator,61joinresidual,60joins,hash,62many-to-manyjoin,61mismatcheddatatypes,63non-equalityjoins,64partialjoin,60queries,60,64spills,65accurateestimation,66hashwarnings,67inaccurateestimation,66memorygrant,65RAIDarray,69SAN,69solidstatedrives(SSD),69splittingTempDB,69SQLproler,67TempDB,68tuning,68Histogram,117eventcounter,117eventle,117eventTracingforWindows,117pairmatching,117ringbuffer,117Hostbusadapter(HBA),332,434INDEX470IImplementchanges,3documentcreation,7environmentmapping,3CMD,4msdbdatabase,4SQLPING,4SQLserveragents,4sysprocessesview,4futuresteps,15limits,12addressrange,13DFF,12localconnections,13loggingandresourcecontrol,15logontrigger,13logontriggers,12parsingIPaddress,13PBM,15RACF,12SERVERSTATE,14SNA,12sys.
dm_exec_connections,13trigger,LOGIN,14WSRM,15newenvironment,5editions,6features,5naming,6patching,6privileges,6SQLserverversions,5reporting,10system-managementprocedure,7EXECUTE,10maningandplacement,7storedprocedure,8tablecreation,8usercreation,7webserver,7version,11compatilityenabling,11exceptionhandling,11Indexing,161businessbubble,191dataintegrity,193description,191indexbusinessusage,191environmentbubble,162description,162missingindexesidentication,162workloadevaluation,170InformationSystemsAuditandControlAssociation(ISACA),222input/outputoperationspersecond(IOPS),362InternetInformationServices(IIS),432Internetprotocol(IP),294I/O,313accesstodata,324diskstalls,324dm_os_wait_stats,326DMV,324OLTP,324query,325rebuild,327resultantdata,325serveractivity,327SPIDs,327statisticsofusage,328sys.
dm_os_schedulers,328T-SQLquery,326backupchanges,322changeintime,323faulttolerance,323middletierservers,324RAIDlevels,323shareddisk,324SLA,324codeordisk,321considerations,315databaseconsolecommands(DBCCs),315diskreads,316IOP,316keyperformanceindicators,316OLTP,317type,317databaseenvironments,313dataset,322environmentalconsiderations,331backupcompression,333compression,333datacompression,333distributedpartitionview,333hostbusadapter,332partititonelimination,333serialized,332SOLIQtest,332workload,332initiatives,314counters,314performancemonitor,314471INDEXmonitoring,314counters,315logicalunitnumber,315storagesubsystem,315queryaddressing,328AdventureWorksDW2008R2,328compressionandrebuild,331criteria,where,330datacache,328FactInternetSales,329GROUPBY,329indexcreation,330messagestab,329reads,329SETSTATISTICSIOON,328sp_helpoutput,330STATISTICSIO,330tactics,317counters,318datacollector,319datatype,320diskreadandwrite,317logicaldisk,counters,321logs,318readandwrite,318size,319TempDBusage,323JJDBC,57Jobs,SQLserver,137KKeyBusinessIndicator(KBI),156LLandingpad,346Localareanetwork(LAN),21Logicalunitnumber(LUN),315MMaintenanceplan,SQLserverdatabaseintegrity,257reportoptions,258tasksorder,256Maintenanceplans,SQLserver,254tasksinwizard,255Managingreleaseschangerequest,198concerns,199ineffectiveprocess,198processoverview,199documents,207notes,208plansandplantemplates,212repository,219requestrecords,207processes,197ineffective,198MicrosoftSQLserver2012,336MicrosoftSQLServer2012,375Microsoft'sTeamFoundationServer,206NNormaldatabasevs.
extrordinaryone,21normalization,22applications,23(Semi-)formal,22Normalization,22applications,23columns,24complexproblems,23entirekeydependency,29key,singleexpression,31uniqueness,table/row,26(Semi-)formal,22Boyce-Coddnormalform,23fthnormalform,23rstnormalform,22fourthnormalform,23OOnlineTransactionalProcessing(OLTP),324Onlinetransactionprocessing(OLTP),362P,QPPPerformanceAnalysisofLogs(PAL),450Performanceguidelines.
SeeGuidelinesPhysicalmodel,33constraints,36addresstable,38domaintale,39keys,37SQLserverconstraints,37datatypes,34approximation,34character,35datatypeprecedence,36INDEX472Physicalmodel,datatypes(cont.
)indexes,35precise,34indexing,39foreignkeys,40uniquness,39Policy-basedmanagement(PBM),15PolicyBasedManagement(PBM),201PowerShell,262automate,263drivespace,263launching,263Processorutilitydatabases,146PublicCompanyAccountingOversightBoard(PCAOB),222RRAIDarray,69Redundantarrayofindependentdiskordisks(RAID),323Relationaldatabasemanagementsystem(RDBMS),395Relationaldatabasemanagementsystems(RDBMS),21RelationalModel,20Releasesdocumentsnotescodeevaluation,210datetracking,209evaluation,210handoff,210information,208name,209performance,211plan,209plancreation,210receiveandanalyse,209requestedplan,209reviewplan,210rollback,210sample,208testing,211plansandtemplates,212activities,214changerequirement,216comparisontool,218developersquestions,214genericinformation,212information,synopsis,213questions,214RedGateSQLtool,218replication,216,218reportgeneration,218snapshotgeneration,218steps,217synopsis,212Requestedchange,releases,198developers,205environment,203database,204multipledevelopment,204preproduction,204production,203qualityassurance,204testing,203goals,200architect/design,202changeincontroll,200churn,202evaluation,203executions,202naming,202performance,201policybasedmanagement(PBM),201practices,201riskreduction,200T-SQLstatement,201processes,205processoverview,199rollback,207versions,206PowerShell,206thirdpartytools,206ResourceAccessControlFacility(RACF),12SSarbanes-Oxley(SOX),221SecureSocketsLayer(SSL),298Securityidentiers(SIDs),260Serverselectionandsizing,361databaseserver,373Dellservers,366comparisons,368PowerEdgeR320,366POwerEdgeR420,367PowerEdgeR520,367PowerEdgeR620,367PowerEdgeR720,368PowerEdgeR720xd,368473INDEXPowerEdgeR820,368formfactor,364I/Ovs.
bladeservers,365processorsocket,365rackmountedservers,365rackunits,364serversize,365towerservers,365licensingcost,373memoryselection,372AMDservers,372businessintelligence,372ashstorage,372prices,ECCDDR3,372RAMprocessing,373settings,372SQLserver,372SSAS,372modelselection,370corelicenses,371DIMMs,371E5-2693and2643,371IntelXeonE5-2690,371licensing,371processorkit,370series,IntelXeonE5,371socketbased,370processorcount,366PCI-E,366physicalcount,366storagesubsystem,366two-socketserver,366servervendor,364rsttier,364socketserver,364variousorganizations,364vendors,364SQLserver2012,362enterpriseeditionfactors,362vendorselection,369AMD,369HP,369inFujitsu,369Intel,370TPC-Ebenchmark,369workloads,369workload,361DMVandDMF,362instances,362IOPS,362queryoptimizer,362singlethreaded,362SLA,431SolidStateDrives(SSD),69SQL2008,299SQLDatababaseSQLserverVs.
SQLdatabasesizeofdatabases,299SQLDatabase,293architecture,294federations,296infrastructure,294instanceavailabilityandfailover,295instancephases,295physicalservers,295costs,311dynamicmanagementviews,304federations,300datatypes,303distributuionnames,300fan-out,303features,300FederationCust,301identity,303inSQLdatabase,301isolatedschema,304limitations,303merge,303rootdatabase,300scenario,302T-SQLchanges,301userinterface,304instance,294instancecosts,311onesizetsall,293services,308BACPAC,310data-tierapplication(DAC),310import/export,310WindowsAzureSQLDataSync,309WindowsAzureSQLReporting,308SQLserverVs.
SQLdatabasebackupsandrestores,299portalfunctions,297security,298SQLdatabasemanagementportal,297SQLservermanagementstudio,297T-SQLsupport,299versions,299WindowsAzuremanagementportals,297troubleshooting,304dashboard,306INDEX474SQLDatabase,troubleshooting(cont.
)DMVs,304executions,305SQLDatabaseManagementPortal(SDMP),297SQLnetworkinterface(SNI),346SQLperformanceanalyzes,458analysisbehaviour,459instances,459I/Operformances,458observations,459clusteredkey,461extraction,transformationandload(ETL),460indexes,460transactionaldatabases,460usagestatistics,460reportingdatabases,460traces,458uniqueblockingchains,460SQLserver,377databaselist,378AdventureWork2008R2,378AOTest,378MirrorMe,378enablingavailability,377propertiesforgroup,382SQLServer,235implementingchanges,3viewpoints,2SQLServer2012,137,153,299SQLserveragent,238alerts,247errorsandseveritylevels,247eventalert,247performanceconditions,248responses,250settingoptions,251WMIevents,249jobDMVqueries,242notications,246options,setup,244runsetup,242schedulingjob,244settingupschedule,245setup,243jobproperties,241jobsactivitymonitor,241creatingDBAInfoandmonitoring,240logs,265failedjobs,266long-runningjobs,266operators,239planscreating,254tasks,252SQLserverintegrationservicesIfObjectExists,260loginoptions,260packageexecution,262taskeditor,260transferlogins,261transfertasks,259SQLServerAnalysisServices(SSAS),372SQLserveranddevelopers,1SQLserverdatamovement,271choosingdeployment,288decisionfactors,288,289dataconsistency,290AlwaysOn,291bulkcopyprocess,292ETLprocessing,291logshipping,291mirroring,291recovery,290redundancy,290datareplication,271disasterrecovery,272globalization,272hardwarefailure,273overnightprocessing,273reportingneeds,272soultions,273advantages,mirroring,281AlwaysOn,282AlwaysOnfailover,285asynchronoustransaction,281availabilitygroups,283benets,transactions,275bulkcopyprocess,287clusteringscenarios,286ETLprocessing,286ETLusingSSIS,286failoverbenetsandlimits,285failoverclustering,284groups,282limitsandbenets,BCP,287logbackups,278logreaders,275logshipping,279merge,276475INDEXmirroringdatabase,280peer-to-peer,276replication,274replication,merge,277scenarios,replication,278snapshot,274synchronoustransaction,281transactionalreplication,274SQLserverDBAarchitecture,396asdataarchitect,405dataow,406Hadoop,405Mapreduce,405bigdata,395archives,426businesscases,426businessintelligence,425businessrules,427discovery,427nonrelationaldataset,426sources,425streamingdata,426unstructured,426dataforanalytics,406Hadoop,407noninteractive,406PowerView,406processsteps,406datatypes,396datawithHadoop,397cluster,397code,398CTP,397datanodes,398framework,397framework,MapReduce,402hardware,404HDFS,397HDFSdataset,399job,MapReduce,402key-valuepairs,403mapfunctioninput,400MapReduce,397MapReduce,nucleus,398output,mapfunction,401tasks,399workow,MapReduce,400Hadoop,396inMicrosoft,427mapred-site.
xml,400nonrelational,bigdata,396usingHadoop,407AdventureWorks,409AzureCTP,410commands,Hive,418,424datapractice,408datasetsbrowsing,412datatransfers,409ecosystem,409environment,410extract,datalter,413HDFS,export,415Hive,416Hiveandexcel,419HiveQL,416importandexport,408importdataow,412JavaScript,420ODBCHivesetup,420Pig,423role,407scriptinHadoop,422Sqooparguments,411Sqoopimport,Azure,413Sqoopjobs,410SQLServerIntegrationServices,259,296SQLServerIntegrationServices(SSIS),252SQLservermanagementstudio(SSMS),340,377SQLServerManagementStudio(SSMS),207,297SQLServerReportingService,296SQLserverreportingservices(SSRS),135,153SQLServerReportingServices(SSRS)mirroring,154ReportServerTempDB,153rpt,schema,153SQLserverVs.
SQLdatabasebackupsandrestores,299copyandrestoreoperations,300operations,299businessedition,299maximumsizeofdatabases,299portalfunctions,297security,298authentication,298featuresinSQLDBs,298rewall,298hashing,298mechanisms,298object-level,298SSL,298sizeofdatabases,299INDEX476SQLserverVs.
SQLdatabase(cont.
)T-SQLsupport,299versions,299SQL2008,299SQLserver2012,299webedition,299WindowsAzuremanagementportals,297SQLTrace,101architecture,101eventnotication,110catalogviews,111servicebrokerarchitecture,111extendedevent,114actions,116eventlifecycle,118eventtracingforwindows,115grouping,118healthsession,121histogram.
SeeHistogram.
NETprovider,123predicates,115typesandmaps,116userinterface(UI).
SeeUserinterfaceleprovider,107infrastructure,101rowset,103.
NETSMOobjectmodel,105serverproler,103traceproperties,104Storagedatautility,144lesizes,144freespaceinformation,145storageareanetwork(SAN),69,431Storageareanetwork(SAN),21,335Structureoftabledataaccessing,21relationaldatabasemanagementsystem,21sys.
fn_hadr_backup_is_preferred_replica,383TTableStructure,databaseutility,141archivetables,142datatables,141healthtables,141Tabulardatastreamprotocol,346TabularDataStream(TDS),294Tasksbackupsandrestoredatabaserecoverymodel,268databaserestores,268databaseintegrity,269monitoringautomationdrivespaces,267lelimits,267spaceindisk,267SQLserveragentlog,265SQLservererrorlog,264WindowsEventViewer,264scheduleddatbaseutility,159Tools,automation,235dynamicmanagementviews(DMVs),237memoryconguration,237query,CPUtime,237viewsessions,238monitoringperformance,235buffermanager,236datacollector,236PowerShell,262automating,263launch,263usingtocheckspace,263SQLserveragent,238alerts,247creatingDBAInfoandmonitoring,240DMVqueries,242jobactivitymonitor,241jobproperties,241jobsetup,243notications,246operarors,239options,setup,244plans,252runsetup,242schedulingjob,244settingupschedule,245SQLserverintegrationservices(SSIS),259Troubleshooting,304dashboard,306queryperformance,307sys.
dm_exec_query_stats,306viewofqueryperformance,307DMVs,304executionplans,305CPUcost,sort,306symbols,305T-SQL,241T-SQLscripts,388logbackups,391SQLagents,388477INDEXtablecreation,390T-SQLstatements,294Tuning,429analyzingdata,446applicationusage,446perfmon,449peakloadanalysisservices(AS),434applications,435assesmentperformance,433capturingdata,436dataconguration,434dene,429IIS,432infrastructure,434integrationservices(IS),434projectllings,432projectteam,430reportingservices(RS),434requirementanalysis,431scenarios,432servers,434storage,434topology,433transactionalvolume,431planningdevise,462sampleplan,462technologies,462workloads,462spills,68Tuningspills,68UUserinterface(UI)eventcreation,126prolerUI,130sessioncreation,125VvirtualIP(VIP),446Virtuallogles(VLF),457Virtuallogles(VLFs),358W,XWaterfallmethod,18WindowsAzureManagementPortal,297WindowsAzureSQLDatabase.
SeeSQLDatabaseWindowsAzureSQLDataSync,309managementportal,309memberdatabases,309synchronizationerrors,309WindowsAzureSQLReporting,308managementportal,308Windowsfailoverclusteringservice(WFCS),376WindowsManagementInstrumentationQueryLanguage(WQL),249WindowsManagementInstrumentation(WMI),146WindowsServerFailoverCluster(WSFC),282WindowsSystemResourceManager(WSRM),15WMIevent,247Y,ZYetAnotherPerformanceProling(YAPP)method,1